Total from first day till selected date

[zahidrahim_ocp]

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

[sunny_talwar]

Unfortunately I cannot

[Anil_Babu_Samineni]

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

[zahidrahim_ocp]

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

[sunny_talwar]

Try this

Sum({<TRANSACTION_DATE = {"=Floor(Only({1}TRANSACTION_DATE)) <= Max(TRANSACTION_DATE)"}>} PRIMARY_QUANTITY)

[Anil_Babu_Samineni]

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

[zahidrahim_ocp]

Same Result

[sunny_talwar]

Can you modify your script to convert Transaction_Date into a true date field?

Date(Floor(TRANSACTION_DATE)) as TRANSACTION_DATE

[zahidrahim_ocp]

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

[sunny_talwar]

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?

[zahidrahim_ocp]

Yes,

I have performed all the above changes and now it is not displaying anything at all.