Aggregation (Sum) with set analysis and total qualifier

[#1]

Hello,

I am looking to combine set analysis with a total qualifier in a summation expression.

For example, let's say I currently have:

    sum(total <YEAR, MONTH> Units)    //intent is to ignore all dimensions except year and month

and i want to add set analysis along these lines:

    sum({$<DIAGNOSIS= ,PROCEDURE= >})    //intent is to clear any user selections for diagnosis and procedure

Is it possible to combine these into one expression? If so, how would the syntax work?

i.e. goal is to ignore specific dimensions and selections.

Thanks in advance for any advice

[Oleg_Troyansky · Accepted solution]

Alysha,

yes, this should be perfectly fine to combine these two features in a  single expression. Together, it would look like this:

sum({$<DIAGNOSIS= ,PROCEDURE= >} total <YEAR, MONTH> Units) 

Keep in mind that Set Analysis expression will be processed prior to building the chart, and this condition disregards user selection in these two fields.

The TOTAL qualifier is performed when the chart is calculated, and the data for the TOTAL aggregation is being supplied from the mini-cube that was prepared for the chart. In this process, the data will be aggregated to the level of Year and Month (assuming that these two fields belong to Chart dimensions).

There is no conflict between the two.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#3]

Oleg - That is exactly what I am aiming to do. Thank you for the thorough explanation! This works perfectly.