Dynamic Measure based on Dimension

[ujjwalraja]

Hi Qlikkies,

I have data in the below format

Product	Year	Sales
P1	2021	20
P1	2022	30
P1	2023	40
P1	2024	50
P2	2021	0
P2	2022	10
P2	2023	20
P2	2024	30

I need to calculate Growth %age at Product Level where the Initial Sales should be greater than Zero. I am looking for solution that can be done at front-end as there is lot of data & I can't drop the rows with Sales as 0.

The desired o/p should be

Product	Initial Non Zero Value	Final Non Zero Value	% Growth
P1	20	50	(50-20)/20
P2	10	30	(30-10)/10

Here for P1 , the initial non zero value will be from 2021 but for P2 the initial non zero value would be from 2022

Please let me know in case of any queries.

[TonySapede]

One idea is to use an if check in the measure to check if the value is > 0. You will also need remove the "Include Zero Valeus" for the table. 

Here is how to add the If check:
If(Sum({<Year={"$(=Min(Year))"}>}Sales)>0,
(Sum({<Year={"$(=Max(Year))"}>}Sales)-Sum({<Year={"$(=Min(Year))"}>}Sales))/Sum({<Year={"$(=Min(Year))"}>}Sales))

You could also build this check in the load script, you don't need to drop the 0 from the fact table, just flag the products you should ignore or build another table with the data you want to show and link by ProductID.

Regards,

[ujjwalraja]

Thanks Tony,

Apologies If I was not clear, The Idea is to get Initial Value for Product P2 in this case bypassing 0, The Solution which you proposed is giving nulls for P2 but it should pick values of next minimum year where the value is non zero.