Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zahidrahim_ocp
Partner - Creator
Partner - Creator

Date Filter to change data

Dear Experts,

I am making an aging report. Suppose i have 2 tables:

Buckets:

Days     Bucket

1                 0-30

2                 0-30

3                 0-30

4                 0-30

5                 0-30

31               31-60

32               31-60

33               31-60

61               61-90

62               61-90

63               61-90

Main_query

TRDate                    Stock_Qty

01-SEP-2017          10

02-SEP-2017          20

05-SEP-2017          30

10-SEP-2017          40

Load Script joins these two tables like below:

Today()-TRDATE  = Days

to get the bucket wise quantity.

Now the issue is instead of a fixed Today() i want to give a date filter on front end and the then buckets should be calculated as per the date selected and data change accordingly.

If this is not possible then what are the workarounds i have.

Regards,

Zahid Rahim

6 Replies
YoussefBelloum
Champion
Champion

Hi,

I think it is a "what if" technique that you need to let the users define the Start date wich is today() in you case.

On qliksense you can do it with extensions, like this one: GitHub - erikwett/qsVariable: Variable extension for Qlik Sense

Hope it helps !

YB

cmsjamesgreen
Contributor III
Contributor III

Hi Zahid

You could create a chart and a Calculated Dimension using an If() statement:

If($(vAgingDate) - TRDATE <= 30, '0 - 30',

   If($(vAgingDate) - TRDATE <= 60, '31 - 60', '61 - 90',

      If($(vAgingDate) - TRDATE <= 90, '61 - 90', '91 +')))

Your expression would be Sum(Stock_Qty).

Tweak the If() statement to incorporate all of your aging buckets.

James

zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

Dear James,

I have created a variable with below syntax:

MAX(FST_RCVD_DATE)

Then create a Master Item Dimension with below syntax:

=if (($(vDate) - FST_RCVD_DATE) <=30,'0-30'

,'<30'

)

Now when i add this dimension it says:

Invalid Dimension

Regards,

Zahid Rahim

cmsjamesgreen
Contributor III
Contributor III

It is likely to be the date format of FST_RCVD_DATE.  This needs to be a Dual or Num field so that QV can calculate the dimension values.

If your FST_RCVD_DATE really is in DD-MMM-YYYY format then I suggest using a Date#() function in the load script to convert it to a standard date format.

Date#(FST_RCVD_DATE, 'DD-MMM-YYYY') as FST_RCVD_DATE

cmsjamesgreen
Contributor III
Contributor III

Also, have you checked MAX(FST_RCVD_DATE) to make sure it is returning a value?

Not applicable

Are you using a filter to select the dynamic TRDATE? If yes, then try GetFieldSelections() ?