Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
roisolberg
Creator
Creator

Set Analysis expression is different in KPI and Bar chart

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

Quality.JPG

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)

Quality2.JPG

 

Any ideas how to do so?

Thanks,

Roi

1 Solution

Accepted Solutions
roisolberg
Creator
Creator

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.

View solution in original post

2 Replies
chrismarlow
Specialist
Specialist

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.  

roisolberg
Creator
Creator

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.

View solution in original post