topic Re: set analysis help in QlikView

set analysis help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: set analysis help

jyothish8807 — Fri, 22 Mar 2019 05:11:26 GMT

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)

Re: set analysis help

jyothish8807 — Fri, 22 Mar 2019 05:17:24 GMT

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.

Re: set analysis help

Anonymous — Fri, 22 Mar 2019 15:24:19 GMT

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.

Re: set analysis help

jyothish8807 — Mon, 25 Mar 2019 06:40:49 GMT

Hi ,

In that case, this should work.