Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create a measure that is a percentage calculated by: 1-(Count of returns/ Sum of quantity)
For each month I'd like to show the calculation on the past 12 months
(for example, for October 20, it will calculate Nov19-Oct20 , so for this example it should be 1-(476/17224)=97.24%
This is the expression I wrote:
1-(
Count({<Year=, Quarter=, Month=, Week=, Date=, Year_Month={">=$(=Date(addmonths(Max(Year_Month), -11), 'YYYY-MMM')) <=$(=Date(addmonths(Max(Year_Month), 0), 'YYYY-MMM'))"} >}Distinct RETURNS)
/
Sum({<Year=, Quarter=, Month=, Week=, Date=, Year_Month={">=$(=Date(addmonths(Max(Year_Month), -11), 'YYYY-MMM')) <=$(=Date(addmonths(Max(Year_Month), 0), 'YYYY-MMM'))"}>}QUANTITY)
)
The KPI seems to be accurate, But my goal is to see it month by month
So i tried to make it in a bar chart but i am losing the 11 months aggregation
the following table shows the calculation for each month,
In the bar chart i am getting the % of each month(marked in Yellow), instead of getting the aggregated % (Red)
Any ideas how to do so?
Thanks,
Roi
Thanks @chrismarlow
I had no success with Above/Rangesum , but in Any case, i've managed to do so using the As-Of table
(https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130)
Many thanks.
Hi,
Generally to get a rolling figure I'd expect you will need to use Above/RangeSum functions to set the dates in each aggregation point (the set analysis that you use to select the dates for the KPI when applied to the chart selects the data for the whole chart that is then divided out by the dimensions as added, the KPI, having no dimension is fine).
If this does not help (i.e. you can't find an example with Above/RangeSum that looks like what you are doing) post some sample/made up data back to this covering the range of dates & the answer you would expect to see in Excel.
Cheers,
Chris.
Thanks @chrismarlow
I had no success with Above/Rangesum , but in Any case, i've managed to do so using the As-Of table
(https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130)
Many thanks.