topic Re: Multiple expression for dimension value in straight table in QlikView

Multiple expression for dimension value in straight table

AKS_Qlik — Sun, 17 May 2020 08:24:32 GMT

Hello,

I need to represent below data in a straight table.

The tittle column would be be a inline/excel load.

In the below scenario each tittle value has a different logic for calculation.

Currently i create three expression's to display Çurrent Year'value for corresponding dimension value.

How can 'Previous Year' and 'Difference ' be represented?

Tittle	Current Year	Previous Year	Difference
Gross margin / Tel .Sales %	500	400	100
Gross margin / Concession Sales %	600	500	100
Gross margin / Main Stores Sales %	900	300	600

Re: Multiple expression for dimension value in straight table

Taoufiq_Zarra — Sun, 17 May 2020 13:17:47 GMT

can you share your sample data and the expected output ?

Re: Multiple expression for dimension value in straight table

Or — Sun, 17 May 2020 13:24:17 GMT

Check the InYear() and InYearToDate() functions. You can shift those to get the previous year(s) and you can subtract e.g. LYTD from YTD to get the difference.

Re: Multiple expression for dimension value in straight table

AKS_Qlik — Sun, 17 May 2020 14:01:42 GMT

Hello,

The issue is not with writing or using any function.Each row has a different logic.

How can i generate a second column as expression for each dimension value?

Re: Multiple expression for dimension value in straight table

AKS_Qlik — Sun, 17 May 2020 14:17:24 GMT

Hello Taoufiq,

The issue is for each row of the dimension the logic is different .Each row is a KPI.

Attached is another snapshot.In the attached scenario for 18 KPI's, 18 expression have been incorporated.

How can another column of expression be included to display values?

Re: Multiple expression for dimension value in straight table

Taoufiq_Zarra — Sun, 17 May 2020 14:29:13 GMT

@AKS_Qlik

you can use ValueList function

for example with this data set :

LOAD * INLINE [
ID
1
2
2
3
];

Add Calculated Dimension... with for example :

=ValueList('KPI1','KPI2','KPI3')

and Mesures :

=pick(match(ValueList('KPI1','KPI2','KPI3'),'KPI1','KPI2','KPI3')
, sum(ID)

,count(ID)

, sum(ID)*count(ID)
)

so each row is a # mesures

Re: Multiple expression for dimension value in straight table

AKS_Qlik — Sun, 17 May 2020 14:45:58 GMT

Thanks Taoufiq for your timely response.Its working fine as per expectation!