1 Reply Latest reply on Jul 25, 2018 2:41 AM by Ankit Bansal

    Calculate sum of measures for each month based on date range

    Timo Wienforth

      Hi everybody,

       

      I'm currently trying to figure out a way to sum all "active" Products in each month.

      The products have a certain start date and a termination date, which look like this (The null values can be ignored):

      What I basically want to do now is create a calculation that sums the "recurring_revenue" between those two dates for each month and display all off  that in a bar chart. 

       

      For example: The bar for April 2015 should show --> 500 + 712,5 + 902,5 + 350 = 2465 because the April 2015 is between the months of start date and termination date for these products.

                             The bar October 2014 should show --> 1.400 +500 = 1.900 because these two products were "active" during this time.

       

      I appended an example workbook. Under "Data" you`ll find the values from above, the Bar Chart Tab should show what i just described.

       

      Thank you guys for your help!!!

       

      Greetings from Germany

      Timo

        • 1. Re: Calculate sum of measures for each month based on date range
          Ankit Bansal

          Timo,

           

          First you need to create a table which will contain all the month year (basically it will be equal to no of  bars in your viz). Lets keep 1st day on month for each record(lets call this AS_OF_DATE). Your input table/excel will look like this

           

          AS_OF_DATE

          2017-01-01

          2017-01-02

          2017-01-03

          .

          .

          .

          2018-11-01

          2018-12-01

           

          Now you need to join your source table  with this table on 1=1 (use calculation field for join and put 1 for both sources.)

           

          Now you need to create 3 calculation columns for each of the date AS_OF_DATE,start_date,termination_date like below:

           

          str(DATEPART('year',[AS_OF_DATE]))+str(DATEPART('month',[AS_OF_DATE]))

           

          lets say 3 calculation names are :

           

          year_month_asofdate

          year_month_startdate

          year_mainth_terminationdate

           

          now create a calculation field revenue_month as

           

          if int(year_month_startdate) <= int(year_month_asofdate) and int(year_mainth_terminationdate) >= int(year_month_asofdate) then recurring_revenue else 0

          end

           

          use as_of_date on your column shelf and revenue_month  on row shelf. And see the bar chart.

           

          Let me know if you find any issue.

           

          Thanks,

          Ankit Bansal