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]