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

 

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)

      

16 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    01-19-2022

    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's avatar
      cartercjb
      ETL
      01-20-2022

      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's avatar
        harikm007
        Data Warehouse
        01-20-2022

        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

  • 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

       

       

      • 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 🙂