Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, Please guide how I can create such table in Qlik Sense:
I have data set like:
SerialKey | Date1 | Date_Cat2 | Money1 | Money2 | Main_category |
1 | 01/11/2015 | 01/11/2015 | 100 | 50 | A |
2 | 01/11/2016 | 01/11/2016 | 200 | 60 | A |
3 | 01/11/2017 | 01/11/2017 | 300 | 100 | A |
4 | 01/11/2018 | 400 | A | ||
5 | 01/11/2019 | 500 | A | ||
6 | 01/11/2020 | 600 | A | ||
1 | 01/11/2015 | 100 | B | ||
2 | 01/11/2016 | 200 | B | ||
3 | 01/11/2017 | 01/11/2017 | 300 | 100 | B |
4 | 01/11/2018 | 01/11/2018 | 400 | 200 | B |
5 | 01/11/2019 | 500 | B | ||
6 | 01/11/2020 | 600 | B |
I want to create a table with following output:
sum of money1 for only those years for which money2 is present for A category | Sum of money2 for only those years for which it is present in its date i.e. date2 for each category | divide sum of money 2 by sum of money1 for those years only for which both of them are present | |
A | 600 | 210 | 210/600 |
B | 700 | 300 | 300/700 |
I tried with this formula sum({<Date1={">=(min(aggr(min(DateCat2),Main_category)) <=(max(agg(max(DateCat2),Main_category))"}>})
But this is giving the wrong answer, it is not aggregating per category, I am in actual getting min of all categories 2015 and max of all categories 2018 and thus the sum is also not coming as desired.
Kindly guide how I can achieve this. Thanks so much in advance.