Multiple expression for dimension value in straight table

[AKS_Qlik]

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

[Taoufiq_Zarra]

can you share your sample data and the expected output ?

[Or]

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.

[AKS_Qlik]

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?

[AKS_Qlik]

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?

[Taoufiq_Zarra · Accepted solution]

@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

[AKS_Qlik]

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