8 Replies Latest reply on Feb 7, 2017 9:24 AM by Vishal D

    Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

    Vishal D

      I have below 3 fields coming from database HANA


      [Year] = [2016,2015,2014,2013]

      [Month] = [1,2,3,4,5,6,7,8,9,10,11,12]

      [Sales] = [123,654,789,234,......]


      I want to show Sales by month bar chart with expand collapse capability so I created hierarchy Year->Month. This works well.


      Issue is on tool tip I want to show Avg Sales Day which is Sales/# of days in either month or year bases on what is shown on chart.


      Initially I want all Years bar chart so Avg Sales day = Sales/# of day in each year but when the user click expand now we are showing chart by month of each year so now the tool tip should show Avg Sales Day = Sales/# of day in each month of a year


      So how do I dynamically calculate this Avg per Day based on where I have yearly chart of monthly chart when user clicks on expand?


      My current formulas :-


      # of days in Year =

      DATEDIFF('day',date(STR([Report Year])+'-01-01'),

      IF ([Report Year]=DATEPART('year',NOW()))

      THEN NOW()ELSE DATE(STR([Report Year])+'-12-31')end)+1


      # of days in Month =

      datediff('day',date(str([Report Year])

      + '-' + str([Report Month]) + '-01'),



      (IF(INT([Report Month])= DATEPART('month',NOW())

      AND(INT([Report Year])= DATEPART('year',NOW())))


      THEN DATE(str([Report Year])

      + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))



      ELSE DATEADD('month',1,date(str([Report Year])

      + '-' + str([Report Month]) + '-01'))END))



      Avg Monthly Sales per Day = IFNULL([Sales],0)/[# of days in Month]


      Avg Yearly Sales per day = IFNULL([Sales],0)/[# of days in Year]