Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andymanu
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 ComponentsDivisionApr-18May-18Jun-18
AnalysingDiv_A100120110
PlanningDiv_A120140125
DesingingDiv_A506060
ImplementingDiv_A201530
TestingDiv_A806090

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 ComponentsDivisionApr-18May-18Jun-18Annualised Coste
AnalysingDiv_A1001201101320
PlanningDiv_A1201401251540
DesingingDiv_A506060680
ImplementingDiv_A201530260
TestingDiv_A806090920

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

4 Replies
fawazeez
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

andymanu
Contributor

Re: Calculate annualized cost based on given monthly cost data

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

fawazeez
Contributor

Re: Calculate annualized cost based on given monthly cost data

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

result.png

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