Qlik Sense - Set Analysis Question

[Anonymous]

Hi all

I need help on understanding Set Analysis in a particular scenario. I have the following table:

Data:

LOAD *,

Year &'-' & Quarter as YearQtr;

LOAD * inline [

Row, Year, Quarter, Amount

1, 2012, Q1, 1100

2, 2012, Q2, 1700

3, 2012, Q3, 1400

4, 2012, Q4, 1800

5, 2013, Q1, 1000

6, 2013, Q2, 1300

7, 2013, Q3, 1100

8, 2013, Q4, 1400

9, 2014, Q1, 500];

I want to build a number of KPI's off this data which would determine

a) Total amount for all quarters

b) Total amount for YTD (in this case 2014)

c) Total amount for Quarters minus last 4 quarters

d) Total amount for Quarters minus last 6 quarters

In my visualizations, I will have filter panel using YearQtr. I also have 4 KPI's per each calculation. Based on the selections made in the filter panel, the amounts for each should change accordingly

For a) my expression wouldn't need Set Analysis, it would simply be a sum of amount, this would never change

For the other 3 however I'm getting varying results.

I'm basing my condition off the Row value e.g. =SUM({$<Row = {"<=$(Row) - 4"}>}Amount) but it's not working.

Any help is appreciated.

Regards

[juraj_misina]

Hi Sean,

How is your Row variable you use in set analysis defined? It should be

=Max(Row)

You also need to turn off YearQtr filter in your set analysis in order to enlarge the data set for the calculation based on your requirements. Then you should be able to elaborate on your expression:

b: SUM({$<YearQtr, Row = {"<=$(Row)"}, Year={$(vMaxYear)}>}Amount)

c: SUM({$<YearQtr, Row = {"<=$(Row) - 4"}>}Amount)

d: SUM({$<YearQtr, Row = {"<=$(Row) - 6"}>}Amount)

Variable vMaxYear is defined as =Max(Year).

Best

Juraj

[Anonymous]

Thanks Juraj, that set me on the right direction. I'm trying to expand on this a little.

The data I have expands to over 10 years worth of values. Using the same table as before, I'm now working the set analysis so that the calculations are generated as per the selection i.e. if I select 2012-Q3, I should get all values between 2012-Q3 and 2011-Q4 for the 1 year aggregation

I'm using a variable to generate the max row number (vMaxQtrSeq), so in line with the above table my expression is something like this:

Sum({<YearQtr, Row = {">=$(=$(vMaxQtrSeq)-3), Row = {"<=$(=$(vMaxQtrSeq)) "} >}  Amount)

But it doesn't work - I think that's down to the use of using the Row condition twice, I've tried to put it in the one strong to no avail:

Row = {"'>=' & $(=$(vMaxQtrSeq)-3), & '<=' &$(=$(vMaxQtrSeq))"}

Any ideas?

[juraj_misina · Accepted solution]

Hi Sean,

yes, you can combine it easily into one set modifier:

Sum({<YearQtr, Row = {">=$(=vMaxQtrSeq-3)<=$(vMaxQtrSeq)"} >}  Amount)

Best

Juraj

[Anonymous]

A slight tweak:

Sum({<YearQtr, Row = {"$(= '>=' & $(=$(vMaxQtrSeq)-3) & '<=' & $(=$(vMaxQtrSeq)))"} >}  Amount)

Thanks for the help Juraj