topic Re: Cumulative data when applying filters in QlikView

Cumulative data when applying filters

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

Hi,

I have a bar chart where I show the sales data for a given country in a cumulative way for the last 12 months. Moreover I have a list box where I can see the months.

The issue I'm having is that when I select a month from the period list box, the bar chart shows the data for the selected month but not taking into account all cumulative sales but just the sales for that month

Taking the example attached, for UK in month 201812 I have a total of 60 sales cummulative. However, when selecting 201812 period from the list box, then the bar chart shows only one bar (the one related to 201812) with 8 sales, and actually what I need is that when I select the month 201812 from the list box, I'd like to get one single bar chart BUT showing 60 sales (rather than 8 sales)

I hope someone can help me

Thanks,

Dani

Re: Cumulative data when applying filters

sunny_talwar — Wed, 24 Apr 2019 11:08:08 GMT

Try this expression with no accumulation selected

=RangeSum(Above(Sum({<Period>}Sales), 0, RowNo())) * Avg(1)

Re: Cumulative data when applying filters

jyothish8807 — Wed, 24 Apr 2019 11:10:21 GMT

Hi Dani,

Try this:

Re: Cumulative data when applying filters

jyothish8807 — Wed, 24 Apr 2019 11:14:58 GMT

Hi Sunny,

Can you please let me know the how Avg(1) allows to show the selected period ?

Thanks in advance!

Re: Cumulative data when applying filters

sunny_talwar — Wed, 24 Apr 2019 11:16:07 GMT

Avg(1) = 0 for non selected periods and Avg(1) = 1 for selected period. Something multiplied with 0 leads to 0.

Re: Cumulative data when applying filters

jyothish8807 — Wed, 24 Apr 2019 11:45:47 GMT

Got it. Thanks a lot 🙂