applying logic based on the dimension values in the table

[taha_mansoor]

Hi, 

I have a scenario whereby I need to put a logical condition in  a measure expression based on the dimension values in the same table chart. For example, I have below data set in my data model:

Year	Month	Value
2013	1	1000
2013	2	2000
2014	1	3000
2014	2	4000

I have to achieve a KPI in the straight/pivot chart whereby I need to apply a logic on first year rows(i.e. rows having year 2013). So, what I want to do in the expression is something like: if year=2013, Value/Month-1. So, the evaluation of this should be 1000/0, 2000/1. Here I am substracting 1 from each value of the column Month refering the Year column value 2013 only. How  exactly I can write this in a straight/pivot chart expression having the dimensions Year and Month in chart as dimensions.

Thanks.

Qlik Sense Desktop 

[Anil_Babu_Samineni]

Perhaps this

Sum(Value)/(Max(Month)-1)

[taha_mansoor]

@Anil_Babu_Samineni thanks for the response but I need to get the logic on Min Year, so even I replace the Max with Min and Month with Year in your expression above, it will take the Year value based on app selections. What if Year has not been selected. I need to apply the logic irrespective of selections i.e. based on dimension value of each row of the straight/pivot table. 

[Anil_Babu_Samineni]

You mean this?

Sum({<Year={$(=Min({1}Year))}>} Value)/(Max({<Year={$(=Min({1}Year))}Month)-1)