show the yearwise data for the single dimension with multiple values

yogi_myd — Sat, 16 Nov 2024 18:10:39 GMT

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.

Re: show the yearwise data for the single dimension with multiple values

Brett_Bleess — Tue, 08 Sep 2020 21:20:20 GMT

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

topic Re: show the yearwise data for the single dimension with multiple values in QlikView

show the yearwise data for the single dimension with multiple values

Re: show the yearwise data for the single dimension with multiple values