# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Calculate annualized cost based on given monthly cost data

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.

Look forward to hearing from someone soon.

Kind regards,

Andy

4 Replies
Contributor

## Re: Calculate annualized cost based on given monthly cost data

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

Contributor

## Re: Calculate annualized cost based on given monthly cost data

Hi Fawaz,

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

Contributor

## Re: Calculate annualized cost based on given monthly cost data

Sorry for not being clear.

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

Honored Contributor

## Re: Calculate annualized cost based on given monthly cost data

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