3 Replies Latest reply: May 18, 2016 5:10 PM by Sunny Talwar RSS

    Create filter on dimension - Distinct only

    Nate slemmons

      Hello all.

       

      I have a data set that has a date field called "weekEnding" for every record in the data set. I want to use that date as a filter but I am having some issues with how to do this. In my Load script I added a dimension called "yearMonth" that I created from the "weekEnding" field. When I add the filter to the sheet I get a "yearMonth" for every record.

       

      Is there a way I can make that filter distinct?

       

      This is my load script.

       

      LOAD

          region,

          plantNumber,

          plant,

          driverID,

          driverName,

          valueType,

          Value,

          Category,

          "total",

          weekEnding,

          sMonth,

          sYear,

          Date(weekEnding,'YYYY-MM') as yearMonth,

          CountOfdriverID,

          driverstatus,

          geoLocation,

          usState

      FROM [lib://DriverScorecard/qryDriverScorecardByWeek.xlsx]

      (ooxml, embedded labels, table is qryDriverScorecardByWeek);

        • Re: Create filter on dimension - Distinct only
          Sunny Talwar

          Try this:

           

          LOAD

              region,

              plantNumber,

              plant,

              driverID,

              driverName,

              valueType,

              Value,

              Category,

              "total",

              weekEnding,

              sMonth,

              sYear,

              Date(MonthStart(weekEnding),'YYYY-MM') as yearMonth,

              CountOfdriverID,

              driverstatus,

              geoLocation,

              usState

          FROM [lib://DriverScorecard/qryDriverScorecardByWeek.xlsx]

          (ooxml, embedded labels, table is qryDriverScorecardByWeek);

          • Re: Create filter on dimension - Distinct only
            Nate slemmons

            Thanks Sunny T. That worked.

             

            I don't understand exactly why adding the Date function cleared up the issue. Can you elaborate?

             

            Thanks,

            Nate

              • Re: Create filter on dimension - Distinct only
                Sunny Talwar

                I did not add just the date function, I added the MonthStart. The issue is that date is just a formatting function and doesn't really remove the day just because it formatted it YYYY-MM. It would still repeat for each day (and time if weekEnding is a timestamp). To get rid of all extra things, I just made all the dates 1st of the month using MonthStart and then assign them your required date format using Date() function.