I got a question with regards to calculating the annualized cost based on the given monthly cost data for different departments.
Assume, the financial year goes from April 2018 to March 2019.
The date format I am using to load data in to Qlik Sense is "MMM-YY". For an example, Apr-18.
Please find the below table in excel for Div_A cost data for last Apr-18, May-18 and Jun-18.
My requirement is to calculate the annualised cost value based on the currently available monthly data (For example, at the moment I got dat for April, May and June only).
Annualised cost = Total cost for each Project Component for the given months) / No of Months *12
Example for Analysing will be = (100+120+110)/3 * 12
Please find the below output table for the expected results,
|Project Components||Division||Apr-18||May-18||Jun-18||Annualised Coste|
Thus, the given number of months will vary over the time (ex - by next month, I will have data for July as well. So the new No of months will be 4) and the total cost of each Project Component will also vary accordingly.
I want to create two variables for the total cost of individual Project Component ( which I am hoping to calculate using cross table) and another variable to calculate the no of months of data provided (currently, it should be 3 which are, "Apr-18", "May-18" and Jun-18).
Also I want to calculate the above requirement during the data load process. Because I will be performing some other calculations after the data is loaded to the respective app.
Appreciate if someone could help me with this issue.
In the table, the first two fields will always be "Project Components" and "Division". The months data will be available from the 3rd column onward.
Thank you in advance.
Look forward to hearing from someone soon.
You can use
CrossTable(YearMonth, Cost, 2)
Than put a pivot chart on dash board with below measure
if(ColumnNo()=0,Avg(Cost)*12, Sum(Cost)) and column as YearMonth
Thanks for your reply.
I did according to what you said but it gave me the total of individual component instead of the annualised value.
However, out of no where, I was able to get the answer using only the cross table and adding a new measure, "=Avg(Cost)*12" to a new field.
However, Thanks you very much for your respond and appreciate a lot if you could explain me the logic behind using of "ColumnNo()=0" statement.
Sorry for not being clear.
i meant you had to Unpivot your data in load script.
and use =Avg(Cost)*12 for the result.
But to able to view all yearmonthwise along with annualised ,
you need to add yearmonth as column and enable Totals.
To control value displayed in total column you can use ColumnNo()=0
ColumnNo() returns the number of the current column within the current row segment in a pivot table. The first column is number 1.
If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter-field sort order.The inter-field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom.
In pivot table, instead of ColumnNo(), you can aslo use Dimensionality() like below.
if(Dimensionality()=0 or SecondaryDimensionality()=0,Avg(Cost)*12, Sum(Cost))