4 Replies Latest reply: Jul 29, 2018 3:07 AM by Bala Bhaskar RSS

    Calculate annualized cost based on given monthly cost data

    Andy Manuja

      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