CONDITIONAL CASE SUM

[cartercjb]

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 of actual hours worked by day by employee.

I am trying to create a dynamic case statement for a metric that calculates the same way as is seen in K7 in the workbook. Essentially it would ready like this:

SUM(TIME ON SUNDAY) 
+ IF (TIME ON MONDAY = 0 THEN 0 ELSE TIME ON MONDAY)
+ IF(TIME ON TUESDAY = 0 THEN 0 ELSE TIME ON TUESDAY)
+ IF(TIME ON WEDNESDAY = 0 THEN 0 ELSE TIME ON WEDNESDAY)
+ IF(TIME ON THURSDAY = 0 THEN 0 ELSE TIME ON THURSDAY)
+ IF(TIME ON FRIDAY = 0 THEN 0 ELSE TIME ON FRIDAY)

Any suggestions on how to get this case statement started through the first two lines?

Many thanks in advance!

-Carter

[harikm007]

@cartercjb 

Are you looking for a formula like this (if I understand the problem statement correctly)?

sum([Day of Week], 
    case when max([Day of Week]) = 1 
    then sum([Time]) 
    else (case	when sum([Time]) = 0 
    			then 8 
   				else sum([Time])
   				end)
    end)

[cartercjb]

Thank you, @harikm007. This is very close. 

How would I update this logic to remain the same, but exclude day 7 from the calculation? The calculation currently is including day 7. If you look at the highlighted row, the calculation should yield ~795, not ~959.

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

THANK YOU!

[harikm007]

@cartercjb 

Please try this calculation

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

-Hari

[cartercjb]

Thanks, @harikm007 . It is giving me a function syntax error and I can't figure out where it is coming from. Based on this screenshot, do you have any idea?

[harikm007 · Accepted solution]

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)

[cartercjb]

Thank you, as always!!

Follow-up question, do you know how I could configure a custom date filter that is = this week, where SUNDAY is the first day of the week, rather than MONDAY?

In our vendor's environment, THIS WEEK filter has MONDAY as the first day of the week. 

[harikm007]

@cartercjb 

I found this article : https://support.sisense.com/kb/en/article/add-week-of-to-custom-hierarchy

As per this article you need to create a custom column and apply filter on that column.

[cartercjb]

@harikm007 Hari - 

I just noticed an issue with this. Our data is 24 hours delayed from real time. So Thursday/Friday haven't happened yet and the calculation is not picking these days up. Any idea if there is a solution to this?

[HamzaJ]

@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]

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

[HamzaJ]

Something like this, you mean?

It requires a bit of tweaking and don't know if its stable/always shows the correct result. 

Do keep in mind that your formula will not work that way. This is due to the fact that you have added a date field in the columns. You should use PREV() in that case. You could try the formula below and do a grand total on column level and change the total-type to SUM for that formula

This will only work if the combination of all rows covers all days. If no one works on sunday, it will never be shown. This would require an alteration in the cube. This is the formula I used to achieve the above example:

(([# of unique EmployeeID],all([Days in Date])) +
case
when (([# of unique EmployeeID],all([Days in Date]))+SUM([Minutes])) = 1 then 480 
else SUM([Minutes]) 
end - ([# of unique EmployeeID],all([Days in Date])))/60

This is what I did:

1. I added ([# of unique EmployeeID],all([Days in Date])) the create a baseline. This counts the unique ID of the employee regardless of the fact if the employee wrote hours for that day. This will create a 1 value for the days without any hours

2. With the CASE-statement we want to filter out days that have hours. when (([# of unique EmployeeID],all([Days in Date]))+SUM([Minutes])) = 1 then 480 by adding essentially 1 + minutes we can divide between worked and not worked days. Worked days will have 1 + X minutes while non worked days will have 1+0=1 minute

3. We subtract ([# of unique EmployeeID],all([Days in Date])) in order to get back to the accurate number of minutes (or 480 if empty)

4. We divide by 60 to get the hours

5. Enable Grandtotal on the column (e.g. date) field

6. Enable Total on the formula and select SUM instead of Auto.

I have not worked out your second requirement of your equation. However perhaps this could help you in the right direction 🙂

[cartercjb]

Thank you, @HamzaJ . I believe it is working correctly for the most part, besides Sundays. I tried adding a metric filter to exclude Sunday but it did not change anything. Where do you suggest I add that filter to exclude Sunday from the +480?

[HamzaJ]

You could try the following formula:

(WTDSum([# of unique Days in Date]),all([EmployeeID]))

This will create a day count:

If first day of the week is Sunday in Sisense, it should count correctly for you. If this works; you could try something like:

case

when (WTDSum([# of unique Days in Date]),all([EmployeeID])) = 1 then 0

else minutes_formula

end 

You could try only WTDSum([# of unique Days in Date]) perhaps that works aswell

[cartercjb]

So this is helping me a ton and I'm on the right track. However, it is now also including people who did not work at all on any of the dates (the first line). 

is there a way to filter them out? @HamzaJ  Thanks.

[cartercjb]

Also, can you explain how you envisioned the PREV() function working here?

[HamzaJ]

Could you try adding minutes-field as filter and filter on "> 0" ? Does this help?

Using PREV() would only be needed if you would like a rolling sum e.g. a cumulatieve sum per day. Is this something you need?