Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

zahidrahim_ocp
Contributor

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
arulsettu
Honored Contributor III

Re: Total from first day till selected date

may be this

sum(sales)/sum(Total Sales)

MVP
MVP

Re: Total from first day till selected date

May be this

Sum({<Date = {"$(='<=' & Date(Max(Date), 'DD-MMM-YYYY'))"}>}Measure)

or this

Sum({<Date = {'<=$(=Max(Date))'}>}Measure)

shraddha_g
Honored Contributor III

Re: Total from first day till selected date

Try

Sum({<DateField ={"<=$(=max(DateField))"}>}Field)

zahidrahim_ocp
Contributor

Re: Total from first day till selected date

Thank you for all of your replies but i want to show Sum from first date till date selected in a filter (it can be any date selected in the filter) not max date.

MVP
MVP

Re: Total from first day till selected date

The above expression will change the max(date) based on your selection in date field

zahidrahim_ocp
Contributor

Re: Total from first day till selected date

I have Date in "TRANSACTION_DATE" and Quantity in "PRIMARY_QUANTITY" I have written below in Master Item but none is working:

Sum({<Date = {"$(='<=' & Date(Max(Date), 'DD-MMM-YYYY'))"}>} PRIMARY_QUANTITY)

Sum({<TRANSACTION_DATE = {"$(='<=' & Date(Max(Date), 'DD-MMM-YYYY'))"}>} PRIMARY_QUANTITY)

Sum({<TRANSACTION_DATE ={"<=$(=max(TRANSACTION_DATE))"}>} PRIMARY_QUANTITY)

MVP
MVP

Re: Total from first day till selected date

How about this

Sum({<TRANSACTION_DATE = {"$(='<=' & Date(Max(TRANSACTION_DATE), 'DD-MMM-YYYY'))"}>} PRIMARY_QUANTITY)

Assuming your transaction_date is in the format DD-MMM-YYYY

zahidrahim_ocp
Contributor

Re: Total from first day till selected date

Dear Sunny,

Transaction Date in filter is with below expression:

=date(floor(TRANSACTION_DATE),'DD-M-YYYY')

So i used:

Sum({<TRANSACTION_DATE = {"$(='<=' & Date(Max(TRANSACTION_DATE), 'DD-M-YYYY'))"}>} PRIMARY_QUANTITY)

It is returning 0

Regards,

Zahid Rahim

MVP
MVP

Re: Total from first day till selected date

This is how it is created in the script?

=date(floor(TRANSACTION_DATE),'DD-M-YYYY')