topic Calculated Metric for last two months in QlikView

Calculated Metric for last two months

anurag0105 — Sat, 16 Nov 2024 20:36:35 GMT

Hi,

In Metric 4 (M4) It is the sum of M1, M2 and M3 till the 3rd last month of the selected till date which simple but things gets messy for calculation of last two months. To calculate M4 for last two months it should take the average of M5 last 3rd and 4th month and than add M1+M2+M3. I am stuck on average part and how to show different calculations in same metric.

Service Month	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019
M1	100	100	100	100	100	100
M2	75	65	12	10	10	5
M3	10	15	5	20	10	5
M4	M1+M2+M3	M1+M2+M3	M1+M2+M3	M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3	AVERAGE(1.20,1.00)+M1+M2+M3
M5	1.50	6.50	1.20	1.00	2.90	2.90
			AVERAGE

Thanks,

Anurag

Re: Calculated Metric for last two months

chrismarlow — Mon, 24 Jun 2019 14:44:40 GMT

Hi,

I think the answer to this is going to be different dependent on how your data is structured, so;

Are M1, M2, M3 & M5 columns in your data, or,

Are M1, M2, M3 & M5 values for a 'Metric' column on a table that also has a Value column, e.g.

Service Month, Metric, Value

3/1/2019, M1, 100

3/1/2019, M2, 75

etc.

Cheers,

Chris.

Re: Calculated Metric for last two months

anurag0105 — Tue, 25 Jun 2019 14:10:34 GMT

hi,

M1,M2,M3 etc are columns in my data.

Regards,

Anurag

Re: Calculated Metric for last two months

chrismarlow — Tue, 25 Jun 2019 20:34:39 GMT

Hi,

Then you should be able to do something like the following.

First create some variables covering the dates of interest (note my date format is DD/MM/YYYY & you may need to do something so the last date gets calculated);

Then you can use an If with TOTAL & some set analysis to only add on the average when you need it (& average the correct two dates);

Let me know if I have misunderstood.

Cheers,

Chris.