Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Limit values from Dimension using List Boxes selection?

    I have Year, Month, Day as List Boxes selection. And in the pivot table, I have Shop as dimension. Now, every shop has ClosingDate and OpeningDate. If shop has no ClosingDate, then it has NULL in it, which means shop is Open.


    Now here, I want to show only those shops in the pivot table, which doesn't have OpeningDate and ClosingDate in the List Boxes selection period. Which means that shop was neither opened nor closed in that List Boxes selection Period.

   Anyone with the scipt for me here? Thanks.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this in script

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

Now in pivot table use this expression.

=Only({<Flag = {0}>} Shop)

Regards,

Jagan.


View solution in original post

3 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this in script

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

Now in pivot table use this expression.

=Only({<Flag = {0}>} Shop)

Regards,

Jagan.


Not applicable
Author

But here, I have the data in form of view from MS SQL Server. Can you convert it into expression? So i may use it in pivot table as Calculated Dimension. Many thanks.

jagan
Luminary Alumni
Luminary Alumni

Hi,

It is very much easier to achieve this in script, also it is efficient.  Try this script

Data:

LOAD

*,

If(Len(Trim(ClosedDate)) > 0 OR  Len(Trim(ClosedDate)) > 0, 1, 0) AS Flag

FROM DataSource;

SQL SELECT

*

FROM SQL_VIEW_NAME;

Regards,

Jagan.