How to Limit values from Dimension using List Boxes selection?

[#1]

    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.

[jagan · Accepted solution]

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.

[#3]

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]

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.