Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
hi,
M1,M2,M3 etc are columns in my data.
Regards,
Anurag
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.