topic Re: limit data in table by variable in QlikView

limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 07:50:48 GMT

Dear all

I am struggling.

I have a table as follows:

To calculate the days overdue I use the expression today()-[Due Date].

Now I want use a variable, e.g. vDay=500 and I like to limit the data in the table

only to show me those rows, where today()-[Due Date] <= 500 is.

I appreciate your help.

Joerg

Re: limit data in table by variable

Anonymous — Fri, 08 Mar 2013 08:09:59 GMT

HI,

1. create a Input box , declare a global variable and attcahe that variable to input box.

2. In your Sum(AMOUNT) expression write this expression if(today()- [Due Date ] <=500,SUM(AMOUNT))

now when you enter a value in input box press enter key by which variable will be set by a value.

to clear variable value create a button named clear and setvariable to 0.

Regards,

ANANT

Re: limit data in table by variable

Fri, 08 Mar 2013 08:10:09 GMT

Go to dimension click on edit Document No dimension and replace with following expression :

=if (Today()-[Due Date]>=500 , [Document No])

Thanks

Re: limit data in table by variable

vikasgupta — Fri, 08 Mar 2013 08:14:15 GMT

Find the sample !!

Re: limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 08:26:23 GMT

Dear Vikas, dear anantmax

I combined both your solutions.

I created a variable vDay and changed the Document No dimension with the following expression :

=if (Today()-[Due Date]<=vDay , [Document No]) and selected suppress zeros.

In the input box I can now "play" with the different values.

Also I created the mentioned button to set the variable:

=max(today()-[Due Date])

This sets the table back to show all values.

Thank you both for your help.

Have a good weekend.

Joerg

Re: limit data in table by variable

Fri, 08 Mar 2013 10:04:06 GMT

Hello Joerg,

You can also avoid using an addition button, by using

=if(Today()-[Due Date] <= if('$(vLimitData)' = '',Today()-[Due Date],$(vLimitData)),[Document No])

instead of your [Document No] dimension.

Please find attached qvw.

Thanks,

iota

Re: limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 10:39:15 GMT

Dear Itsangad

A very nice solution. This should fit my needs best.

Have a good weekend.

Joerg

Re: limit data in table by variable

Fri, 08 Mar 2013 10:41:31 GMT

Thanks Joerg !

Please mark the question as answered.

Have fun !

Thanks,

itsangad

Re: limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 14:23:55 GMT

Dear Itsangad

Do you have also an idea how I can achieve this ?

Joerg

Re: limit data in table by variable

Fri, 08 Mar 2013 15:10:43 GMT

Hello Joerg,

The same has been implemented in the attached qvw.

Please let me know, if you find it helpful or anything else is required.

Thanks,

itsangad

Re: limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 15:32:31 GMT

Dear Itsangad

Seems perfect. I'll check in my final app if it works.

Thanks a lot.

Joerg

Re: limit data in table by variable

Fri, 08 Mar 2013 15:40:57 GMT

Sure, no problem. I just missed to remove the sum(Amount) column and also the rename of labels.

Let me know if in any other way I can help.

Thanks,

itsangad

Re: limit data in table by variable

omyahamburg — Fri, 08 Mar 2013 18:59:43 GMT

Dear Itsangad

Don't worry, everything is fine so far. As I'm already at home without access to my server I'll check further with my app on Monday.

If I have problems I appreciate If I could count on further help from you.

enjoy your weekend.

Joerg