Calculate annualized cost based on given monthly cost data

[andymanu]

Hi,

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.

Project Components	Division	Apr-18	May-18	Jun-18
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

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
Analysing	Div_A	100	120	110	1320
Planning	Div_A	120	140	125	1540
Desinging	Div_A	50	60	60	680
Implementing	Div_A	20	15	30	260
Testing	Div_A	80	60	90	920

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.

Requirement

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.

Kind regards,

Andy

[fabdulazeez]

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

[andymanu]

Hi Fawaz,

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.

Kind regards,

Andy

[fabdulazeez]

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

[balabhaskarqlik]

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))