Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I want to show the total quantity from the first day the data is available till the date selected in filter pane.
Suppose i have data from 01-JAN-2000 to 13-SEP-2017. Now if i selected date 13-SEP-2017 in a filter then Qlik should show the total records/sum from 01-JAN-2000 till 13-SEP-2017.
Regards,
Zahid Rahim
Unfortunately I cannot
This is related to sunny's expression only
Sum({<Date = {"$(='<=' & Date(Max(Date), 'DD-MMM-YYYY'))"}>}Measure)
Try this way?
Create 2 variables
LET vStart = Min(Date); // Returns 01-JAN-2000
LET vEnd = Max(Date); // Returns 13-SEP-2017
Then Set analysis, This?
Sum({<Date = {">=$(=vStart) <=$(=vEnd)"}>}Measure)
OR for Dynamic
Sum({<Date = {">=$(=vStart) <=$(=Max(Date))"}>}Measure)
Note - Manage your date formats
Dear Anil,
Thank you for the reply. It keeps me giving the total value for only the selected date. I have tried both option after creating variables.
Regards,
Zahid Rahim
Try this
Sum({<TRANSACTION_DATE = {"=Floor(Only({1}TRANSACTION_DATE)) <= Max(TRANSACTION_DATE)"}>} PRIMARY_QUANTITY)
This is really weird behavior, May be generate random numbers in your application and Dates from 2000 to till date then share that qvf file. Will surely look into that
Same Result
Can you modify your script to convert Transaction_Date into a true date field?
Date(Floor(TRANSACTION_DATE)) as TRANSACTION_DATE
Dear Sunny,
I have changed my script to below:
[mtl_material_transactions]:
LOAD TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LOCATOR_ID,
SUBINVENTORY_CODE,
Date(Floor(TRANSACTION_DATE)) AS TRANSACTION_DATE,
PRIMARY_QUANTITY
FROM [LIB://QVD/mtl_material_transactions.qvd] (qvd);
But no luck. !
Regards,
Zahid Rahim
This might be a stupid question, but did you reload your app after changing the script? If you have, did you change your filter pane to just TRANSACTION_DATE instead of the expression you were using easlier?
Also, try this expression with the above script change
Sum({<TRANSACTION_DATE = {"$(='<=' & Date(Max(TRANSACTION_DATE))"}>} PRIMARY_QUANTITY)
If it still doesn't help, do you make selection in another date and time related field other than TRANSACTION_DATE?
Yes,
I have performed all the above changes and now it is not displaying anything at all.