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

Total from first day till selected date

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

36 Replies
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


Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
zahidrahim_ocp
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

Yes,

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