3 Replies Latest reply on May 3, 2018 5:42 AM by Okechukwu Ossai

    Issue: Wrong order days of last and current month

    Patrick Weber

      Hi I have a question about showing the exact order of previous days. In this example (see attached file) I created a field DateDisplay where automatically

      the view will change if there is an selection on my parameter TimeFilter.

      My issue: E.g. if I say the last 6 days (today is May 3) it will show all days where data exists. The problem now May 1st ist at the first position and not behind 30 (April 30). How can I solve this problem?

       

      Thanks for any tips.

        • 1. Re: Issue: Wrong order days of last and current month
          Okechukwu Ossai

          Hi Patrick,

           

          The issue is that [DateDisplay] is a string. So, sorting is alphabetic by default. You'll need to create a date field to use for sorting. Somehow, the [calcDate] field was broken. So, I modified it too. See the steps below.

           

          Step 1: Create calculated field [calcDate]

          IF [TimeFilter] = 'Year' AND DATEDIFF('year' , [Date], TODAY()) >0 and DATEDIFF('year', [Date], TODAY())<= [TopN 1] THEN 1

          ELSEIF [TimeFilter] = 'Quarter' AND DATEDIFF('quarter' , [Date], TODAY()) >0 and DATEDIFF('quarter', [Date], TODAY())<= [TopN 1] THEN 1

          ELSEIF [TimeFilter] = 'Month' AND DATEDIFF('month' , [Date], TODAY()) >0 and DATEDIFF('month', [Date], TODAY())<= [TopN 1] THEN 1

          ELSEIF [TimeFilter] = 'Week' AND DATEDIFF('week' , [Date], TODAY()) >0 and DATEDIFF('week', [Date], TODAY())<= [TopN 1] THEN 1

          ELSEIF [TimeFilter] = 'Day' AND DATEDIFF('day' , [Date], TODAY()) >0 and DATEDIFF('day', [Date], TODAY())<= [TopN 1] THEN 1

          ELSE 0 END

          Put [calcDate] on the filter shelf and set it to 1.

           

          Step 2: Create calculated field [DateDisplay Sorting]

          DATE(IF [TimeFilter]= 'Year' THEN DATETRUNC('year',[Date])

          ELSEIF [TimeFilter] = 'Quarter' THEN DATETRUNC('quarter',[Date])

          ELSEIF [TimeFilter] = 'Month' THEN DATETRUNC('month',[Date])

          ELSEIF [TimeFilter] = 'Day' THEN [Date]

          ELSEIF [TimeFilter] = 'Week' THEN DATETRUNC('week',[Date])

          END)

          Step 3: Sort [DateDisplay] using [DateDisplay Sorting]

           

          Hope this helps.

          Ossai