Hi Bala,
I tried both the codes but unfortunately still it's not giving me the correct answer.
Let me explain what I am doing may be then it might help you to get a proper understanding.
I am calculating an annualised amount based on the given input number of month's data.
Please refer my data set,
| Project Components | Division | April | May | June |
| Analysing | Div_A | 100 | 120 | 110 |
| Planning | Div_A | 120 | 140 | 125 |
| Desinging | Div_A | 50 | 60 | 60 |
| Implementing | Div_A | 20 | 15 | 30 |
| Testing | Div_A | 80 | 60 | 90 |
The above data set is for a single division and similarly, I got another two divisions with similar data sets.
The calculation of the annualised amount is = Total Cost of each activity(Analysing, designing, etc) / 3 (which is the number of months) * 12 ( to get the annualised value).
Please find my code below,
Div_A_Actuals:
CrossTable (Month,Costs,2)
LOAD
"Project Components",
Division,
April,
May,
June
FROM [lib://Actual_Data/Actual Division Costs.xlsx]
(ooxml, embedded labels, table is [Div-A]);
Temp:
Load "Project Components",
Date(Date#(Month,'MMM'),'MMM') as Month,
Costs
Resident Div_A_Actuals;
Drop Table Div_A_Actuals;
Final:
Load *,
//Count(DISTINCT Month) as NoOfMonths
('$(RangeCount(above(Count(Distinct Month),0,3)))') as NoOfMonths
From [lib://Actual_Data/Temp.QVD] (qvd);
Note
At the moment since I got only 3 months data, the variable value of the "NoOfMonths" should be "3". However, according to my commented code and using of your provided code, it still shows the variable value as 15 (which is "5 Project Components" multiplied by 3.
Hope now it is much clear and look forward to hearing from you.
Thanks
Andy
