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)

      

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

 

 

HamzaJ's avatar
HamzaJ
Data Integration
02-14-2022

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's avatar
    cartercjb
    ETL
    02-15-2022

    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's avatar
      HamzaJ
      Data Integration
      02-15-2022

      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's avatar
    cartercjb
    ETL
    02-16-2022

    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's avatar
    cartercjb
    ETL
    02-16-2022

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

    • HamzaJ's avatar
      HamzaJ
      Data Integration
      02-16-2022

      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?