Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

set analysis help, i think

Good Afternoon;

    I hope this is the right forum, I inherited a dashboard i am trying to understand, its source comes from a spreadsheet.

sum ( {$<[Ops Year] = {$(#=Only(_YYYY))}, [Ops Month] = {"<=$(#=Only(_MM))"},[Division]={'A','B','C','D','E','F','G'}>} [Bookings]/1000000)
/
sum ( {$<[Ops Year] = {$(#=Only(_YYYY))}, [Ops Month] = {"<=$(#=Only(_MM))"},[Division]={'A','B','C','D','E','F','G'}>} [Revenue-Actual]/1000000)

Th calculation works however what i need to change is that instead of the values from one month i need the sum of all months[Ops Months].  This would only be for the denominator. 

the number for January I am getting is .19 which is correct as it is only one month, however for February i need the number to roll both January and February numbers in revenue.

Based on the selection of the [Ops month] is should calculate the revenue amount for everything less than or equal to for that selection for the divisions stated, any ideas?  I have attached a sample of the source data for the calculation

1 Reply
Claudiu_Anghelescu
Specialist
Specialist

sum ( {$<[Ops Year] = {$(#=Only(_YYYY))}, [Ops Month] = {*},[Division]={'A','B','C','D','E','F','G'}>} [Bookings]/1000000)
/
sum ( {$<[Ops Year] = {$(#=Only(_YYYY))}, [Ops Month] = {*},[Division]={'A','B','C','D','E','F','G'}>} [Revenue-Actual]/1000000)
To help community find solutions, please don't forget to mark as correct.