topic Re: Displaying only selected range for cumulative total in QlikView

Displaying only selected range for cumulative total

Anonymous — Tue, 07 Aug 2018 07:06:10 GMT

Hi,

Can anybody help me or guide with the following problem.

I have a simple bar chart with month-year dimension and expression displaying cumulative total like:

=RangeSum(Above(Count(DISTINCT {$<%DateType={'SpecialDate'}>} %UniqueID), 0, RowNo()))

When I make selection in the month-year pane cumulative total is recalculated. It is correct. But i want to know is it possible to calculate the cumulative data regardless of the month-year (over full set of all the records), but display only selected range? So if i select 3 months (august 2017 - october 2017) there are only 3 values (39, 51, 60) should be represented:

Re: Displaying only selected range for cumulative total

sunny_talwar — Tue, 07 Aug 2018 11:38:05 GMT

Try this

=RangeSum(Above(Count(DISTINCT {$<%DateType={'SpecialDate'}, MonthYear>} %UniqueID), 0, RowNo())) * Avg( {$<%DateType={'SpecialDate'}>} 1)

Re: Displaying only selected range for cumulative total

Anonymous — Wed, 08 Aug 2018 09:07:45 GMT

Thank you, that was helpful:

* Avg({<Дата = {">=$(=$(vStartDateNum))<=$(=$(vEndDateNum))"}>} 1)

but I still don't understand how it works

Re: Displaying only selected range for cumulative total

sunny_talwar — Wed, 08 Aug 2018 11:05:55 GMT

Basically within the rangesum expression... we ignore selection in MonthYear field... which forces it to show everything regardless of selection... but we use Avg(1) to show only those MonthYear... which have been selected... as Avg(1) = 1 for selections and Avg(1) = 0 for out of selection.... So, you accumulation is still intact, but you are just showing the selected MonthYears

Re: Displaying only selected range for cumulative total

Anonymous — Wed, 08 Aug 2018 11:35:41 GMT

Now much clearer for me. Thank you!