Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nslemmons
New Contributor II

Create filter on dimension - Distinct only

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);

1 Solution

Accepted Solutions
MVP
MVP

Re: Create filter on dimension - Distinct only

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);

3 Replies
MVP
MVP

Re: Create filter on dimension - Distinct only

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);

nslemmons
New Contributor II

Re: Create filter on dimension - Distinct only

Thanks Sunny T. That worked.

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

Thanks,

Nate

MVP
MVP

Re: Create filter on dimension - Distinct only

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.