2 Replies Latest reply on Sep 20, 2017 7:17 AM by Neil Lord

    Dynamically choose the date range in X Axis based on the chosen parameter

    Sudha Viswanathan

      Hi,

      I am dynamically displaying either days/months/weeks in the X Axis as per the chosen parameter as shown below:

      CASE [Frequency of Data Load:]

      WHEN "Daily" THEN DATETRUNC('day', [fulldt])  

      WHEN "Monthly" THEN DATETRUNC('month', [fulldt])

      WHEN "Weekly" THEN DATETRUNC('week', [fulldt])

      END

      Now, I want to display only the last n days if it's daily, last n months if it's monthly and last n weeks if it's weekly.  I am trying to do something as shown below:

      CASE [Frequency of Data Load:]

      WHEN "Daily" THEN (DATETRUNC('day', [fulldt]) >= dateadd('day',-60,WINDOW_MAX(max([fulldt]))) 

      WHEN "Monthly" THEN (DATETRUNC('month', [fulldt]) >= dateadd('month',-6,WINDOW_MAX(max([fulldt]))) 

      WHEN "Weekly" THEN (DATETRUNC('week', [fulldt]) >= dateadd('week',-15,WINDOW_MAX(max([fulldt]))) 

      END

      The above piece code doesn't work.  Please help me code this the right way.

       

      Thanks,

      Sudha