# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
cancel
Showing results for
Did you mean:  Contributor II

## Qlik Sense - Set Analysis Question

Hi all

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

Data:

Year &'-' & Quarter as YearQtr;

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

1 Solution

Accepted Solutions  Partner

Hi Sean,

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

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

Best

Juraj

4 Replies  Partner

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  Contributor II
Author

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?  Partner

Hi Sean,

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

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

Best

Juraj  Contributor II
Author

A slight tweak:

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

Thanks for the help Juraj 