Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
Also, have you checked MAX(FST_RCVD_DATE) to make sure it is returning a value?
Are you using a filter to select the dynamic TRDATE? If yes, then try GetFieldSelections() ?