36 Replies Latest reply: Sep 18, 2017 5:58 AM by zahid Rahim

# 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

• ###### Re: Total from first day till selected date

may be this

sum(sales)/sum(Total Sales)

• ###### 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)

• ###### Re: Total from first day till selected date

Try

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

• ###### 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.

• ###### Re: Total from first day till selected date

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

• ###### 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)

• ###### Re: Total from first day till selected date

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

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

• ###### 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

• ###### Re: Total from first day till selected date

This is how it is created in the script?

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

• ###### Re: Total from first day till selected date

NO, I have formatted it in filter pane expression.

In script it is coming as it is with timestamp information.

• ###### Re: Total from first day till selected date

What format do you see when you use this in filter pane?

TRANSACTION_DATE

• ###### Re: Total from first day till selected date

It is coming with timestamp and thouands of duplicate date.

• ###### Re: Total from first day till selected date

That is okay, but what is the format? May be share an image

• ###### Re: Total from first day till selected date

Is this TRANSACTION_DATE or =date(floor(TRANSACTION_DATE),'DD-M-YYYY')? I am looking for a filter pane with just TRANSACTION_DATE... would you not be able to do that?

• ###### Re: Total from first day till selected date

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

Title is: TRANSACTION_DATE

• ###### Re: Total from first day till selected date

I am sorry man... but you are not giving me what I am asking for... I wonder how can I help you ....

Best of luck

• ###### Re: Total from first day till selected date

Do u have skype id? or can help me over Team Viewer.

• ###### Re: Total from first day till selected date

Unfortunately I cannot

• ###### Re: Total from first day till selected date

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

• ###### Re: Total from first day till selected date

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

• ###### Re: Total from first day till selected date

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

• ###### Re: Total from first day till selected date

Try this

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

• ###### Re: Total from first day till selected date

Same Result

• ###### Re: Total from first day till selected date

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

Date(Floor(TRANSACTION_DATE)) as TRANSACTION_DATE

• ###### Re: Total from first day till selected date

Dear Sunny,

I have changed my script to below:

[mtl_material_transactions]:

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

• ###### Re: Total from first day till selected date

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?

• ###### Re: Total from first day till selected date

Yes,

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

• ###### Re: Total from first day till selected date

Missed a closing parenthesis... try this

Sum({<TRANSACTION_DATE = {"\$(='<=' & Date(Max(TRANSACTION_DATE)))"}>} PRIMARY_QUANTITY)

• ###### Re: Total from first day till selected date

Dear Sunny,

Issue seems resolved but i am unable to see balance on the date where there is no activity. Suppose item has one record at 31-JUL-2017 and dont have any record at 30-JUL-2017.

On Selecting 31-JUL-2017 it is showing balance till 31st. But selecting 30-JUL-2017 it does not show that item but user want to see its balance on till 30th as well.

What can be the approach.

Regards,

Zahid Rahim

• ###### Re: Total from first day till selected date

I can't seem to know why it would do that... I will need to see it to know why it is doing it in your case... can you share a sample?

• ###### Re: Total from first day till selected date

Dear Sunny,

We are able to see balance from first date till 31 Jul 2017. But user cannot select 30 Jul 2017 as there is no record for this item in transactions table on 30 Jul 2017.

Requirement is if user select 30 Jul 2017 then all items should show with the sum from first date till 30 Jul 2017 irrespective of their records in 30 Jul 2017.

Hope you understood.

Regards,

Zahid Rahim

• ###### Re: Total from first day till selected date

Then I guess you are dealing with Missing data, right? I guess you need to use Generating Missing Data In QlikView to populate the missing data in your dashboard

• ###### Re: Total from first day till selected date

Dear Sunny,

Balances are calculated correctly but when i want to see 2 dates balances and select 30 and 31 JUL 2017. It shows all previous dates balance as well. rather than showing on 30 and 31 Sep 2017 two dates balances.

Regards,

Zahid Rahim

• ###### Re: Total from first day till selected date

Hi Zahid,

Try this,

Sum({<Date={">=1(=Min(Date)) <=\$(=Max(Date))"}>}sale)

Regards,