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)

      

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

cartercjb's avatar
cartercjb
ETL
01-20-2022

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

      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. 

       

    • cartercjb's avatar
      cartercjb
      ETL
      01-27-2022

      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?