Forum Discussion

cartercjb's avatar
cartercjb
ETL
01-19-2022
Solved

CONDITIONAL CASE SUM

Hi Sisense Community!! Please reference the attached workbook that I drafted in Excel.  I am trying to draft a daily view by week, of each employee's time worked. This screenshot shows an example o...
Blox
Code-first Analytics
dashboards & reporting
data models
  • harikm007's avatar
    harikm007
    01-20-2022

    oops.. I missed the keyword 'THEN'. 

    Here is the updated formula (added 'THEN' before second CASE statement)

    sum([Day Of Week],
    case 
    when max([Day Of Week]) = 1 
        then sum([Employee Regular Paid]) 
    when max([Day Of Week]) < 7 
       then (case when sum([Employee Regular Paid])= 0 
    			then 8 
   				else sum([Employee Regular Paid])
   				end)
    end)

      

HamzaJ's avatar
HamzaJ
Data Integration
01-19-2022

cartercjb 

If I understood it correctly , I would do something like this:

 

(SUM([TIME]), [Day of Week]) (click on Day of Week and filter on sunday)
+
if((SUM([TIME]), [Day of Week])=0,0,SUM([TIME], [Day of Week])) (click on Day of Week and filter on monday)
+
if((SUM([TIME]), [Day of Week])=0,0,SUM([TIME], [Day of Week])) (click on Day of Week and filter on tuesday)
rince and repeat

 

SUM([TIME], [Day of Week]) will sum the time filtered with a specific parameter (in this case date)

This will not work if you use date-field as columns as the formula is applied per day and thus each day will be added to sunday individually. 

If you want to to be a running sum you should filter previous days aswell when progressing through the week, e.g. tuesday will be filtered on monday and tuesday

 

cartercjb's avatar
cartercjb
ETL
02-14-2022

Hi HamzaJ -

Thanks for your reply - it was very informational. I'm still running into a snag when a day's value is 0/null. I'll try my best to explain..

I currently have this date filter set to view last week from SUN (02/06) through SAT (02/12). 

 The screenshot below gives the actual values for each of the days worked, which is correct. 

To simplify my original equation, my goal is to record the actual hours worked through Wednesday and then:

  • IF a day is a weekday AND no hours were actually worked, then +8 for each of those weekday(s)
  • OR a weekday has not yet occurred (Thursday, Friday), then +8 for each of those weekday(s)

This excel model shows how it should be calculating the final values highlighted in green. 

However, the formula expression below is only counting the actual values. 

 

((SUM([Employee Regular Paid]),[SUNDAY])
+IF((SUM([Employee Regular Paid]),[MONDAY])=0,8,(SUM([Employee Regular Paid]),[MONDAY]))
+IF((SUM([Employee Regular Paid]),[TUESDAY])=0,8,(SUM([Employee Regular Paid]),[TUESDAY]))
+IF((SUM([Employee Regular Paid]),[WEDNESDAY])=0,8,(SUM([Employee Regular Paid]),[WEDNESDAY]))
+480 (FOR THURSDAY)
+480 (FOR FRIDAY)
)/60

 

Any ideas/suggestions as to why it is not picking up the 8 value when a day is null/0?

Thanks!!!

-Carter