Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i am having below structure like tables,joined by A1 and A2, for the Selection year=2020 and quarter=q2 and A1=x and A2=y
Table1 | |||||
year | quarter | Week | A1 | A2 | sales |
2018 | q2 | 24 | x | y | 100 |
2019 | q2 | 24 | x | y | 200 |
2020 | q2 | 24 | x | y | 100 |
Table2 | ||||
YR | QTR | A1 | A2 | Cat |
2019 | 2 | x | y | apple |
2020 | 2 | x | y | ball |
Output i am having in straight table , by
Sum({<YR={"$(=only(Year))"},QTR={'2'}>}$(vSales))
Sum({<YR={"$(=only(Year)-1)"},QTR={'2'}>}$(vSales))
Dimensions | Dimensions | Dimensions | Expression | Expression | |
Cat | A1 | A2 | Sumsales CY | Sumsales Py | Count(Sales units) |
Ball | x | y | 100 | 0 | 1 |
apple | x | y | 0 | 200 | 1 |
For the same selection, user expecting only Ball but with previous year data in straight table and cat's count
Dimensions | Dimensions | Dimensions | Expression | Expression | |
Cat | A1 | A2 | Sumsales CY | Sumsales Py | Count(Sales units) Py |
Ball | x | y | 100 | 200 | 1 |
only(aggr(distinct Cat,year,quarter)) is not working in dimensions, the selection objects is year=2020 and quarter=q2 and A1 =X and A2=y. i am struck in calculating the py sales and py count because of the dimension is Cat. i am not sure this scenario is the slowly changing dimensions.i dont have much idea now.is any one overcome the same scenario ? any advice ? Thanks in advance.
Regards
Yogesh.
Have a look at the following Design Blog post, may be what you need, not sure:
https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822
Regards,
Brett