topic Re: How to Limit values from Dimension using List Boxes selection? in QlikView

How to Limit values from Dimension using List Boxes selection?

Mon, 25 Nov 2013 07:36:02 GMT

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.

Re: How to Limit values from Dimension using List Boxes selection?

jagan — Mon, 25 Nov 2013 07:46:46 GMT

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.

Re: How to Limit values from Dimension using List Boxes selection?

Mon, 25 Nov 2013 08:03:01 GMT

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.

Re: How to Limit values from Dimension using List Boxes selection?

jagan — Mon, 25 Nov 2013 10:17:46 GMT

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.