set analysis help

[Anonymous]

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

[jyothish8807]

Hi Rlhardin,

Try this this both, since i am not clear with the exact requirement. Not sure why you have '#' you can remove it if required.

//The first expression will consider Jan & Feb in nominator (if you select Feb) and all months in denominator.

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

//This expression will consider the current month you selected in nominator (Feb for example) and Jan &Feb in denominator.

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)

[jyothish8807]

Hi Rlhardin,

Try this this both, since i am not clear with the exact requirement. Not sure why you have '#' you can remove it if required.

1.The first expression will consider Jan & Feb in nominator (if you select Feb) and all months in denominator.

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

2.This expression will consider the current month you selected in nominator (Feb for example) and Jan &Feb in denominator.

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)

[Anonymous]

Hi KC;

    thank you for the response. Here is a bit more detail.

So for January bookings is divided by January revenue
then 
     for February bookings is divided by the sum of January + February revenue

January works fine, in getting the .19 number however my calculation below for February is showing .58 when it should be .31
this is due to for February I am not getting the sum total in revenue, i am just getting February. 

I attached a screen shot of what I am trying to accomplish.

[jyothish8807]

Hi ,

In that case, this should work.

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)