Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zahidrahim_ocp
Contributor

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

Tags (2)
6 Replies
YoussefBelloum
Esteemed Contributor

Re: Date Filter to change data

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
New Contributor III

Re: Date Filter to change data

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
Contributor

Re: Date Filter to change data

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
New Contributor III

Re: Date Filter to change data

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
New Contributor III

Re: Date Filter to change data

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

Not applicable

Re: Date Filter to change data

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

Community Browser