topic Showing only a set of dimension values while using rangesum? in QlikView

Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 06:37:27 GMT

Hi,

I have this table where "rangesum" is summing up the current and previous values in "w/o rangesum".

I need to find out if it is possible to see only 2016 data on the dimension, but should have the same values in "rangesum" as is now as shown in the second picture?

FYI: the expression behind rangesum is similar to-

=rangesum(above((Count({<cond1,cond2,cond3>} distinct field)),0,RowNo()))

Does anybody have an idea on this?

Re: Showing only a set of dimension values while using rangesum?

dsharmaqv — Fri, 06 Jan 2017 07:11:35 GMT

extract year from date field and create a year list object(dynamoc option).

like this

=Year(Date#([RCA Period],'YYYY-MMM'))

or you can use write set analysis

Re: Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 10:32:22 GMT

HI Deepak,

Thank you for the response.

Do you mean creating a list box with =Year(Date#(BDate,'YYYY-MMM')) as expression and selecting 2016 in the listbox?

If this is what you meant, then when I select 2016, this would limit the data only to 2016 and the rangesum would not work as expected i.e, the rangesum would take only the sum of this year but not include the previous years.

Any thoughts?

Re: Showing only a set of dimension values while using rangesum?

sunny_talwar — Fri, 06 Jan 2017 10:41:42 GMT

Try this:

=RangeSum(Above(Count({<cond1,cond2,cond3>} DISTINCT field), 0, RowNo())) * Avg(1)

Re: Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 10:50:55 GMT

HI Sunny,

I tried your expression & this is what I get-

This looks good, but how do I see only 2016 data?

Re: Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 10:54:24 GMT

When I select 2016 in the list box I get this-

but I am trying to achieve this-

To be able to see only 2016 data but should include data from 2015 too

Re: Showing only a set of dimension values while using rangesum?

sunny_talwar — Fri, 06 Jan 2017 10:56:46 GMT

Ignore selection in Year field here:

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * Avg(1)

I thought one of your conditions above included Year already

Re: Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 11:18:05 GMT

Hi Sunny,

Ain't working.

Here is a sample qvw.

Re: Showing only a set of dimension values while using rangesum?

sunny_talwar — Fri, 06 Jan 2017 11:21:55 GMT

You want the first expression also?

Just the RangeSum1 expression in itself is working, in case you need the other one... use this expression:

=above((Count({<Year,[BMonth-Year]>} distinct CODE)),0,RowNo()) * Avg(1)

Re: Showing only a set of dimension values while using rangesum?

divya_anand — Fri, 06 Jan 2017 12:02:29 GMT

Sunny,

This works perfect. However, I have a question.

When we select 2016, the data that is associated with 2016 only has to be available right? How is it that even 2015 data is included on selection of 2016?

Re: Showing only a set of dimension values while using rangesum?

sunny_talwar — Fri, 06 Jan 2017 12:11:14 GMT

First part of the expression

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * Avg(1)

Is ignoring Year selection, so any selection you make, all the data is available to view

Second part of the expression

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * Avg(1)

Is 1 for your selections and 0 for out of selections. So in your example, Avg(1) = 1 for 2016 and Avg(1) = 0 for 2015

For 2016

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * 1

For 2015

=RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo())) * 0

Alternative to using this is to use If statement

If(Count(DISTINCT field) > 0, RangeSum(Above(Count({<cond1,cond2,cond3, Year>} DISTINCT field), 0, RowNo()))

Where again, Count would be 0 for out of selection