Solved: Re: Calculate today and yesterday total of sales - Qlik Community

razvan_brais · ‎2016-09-26

Hy guys , I have a problem. I`m trying to calculate the total value of sales for the last day in table and total value of sales for the previous day.

For the last day I`ve calculated this way :

=sum({<DATE={'$(=max(DATE))'}>} Sales)

And for the previous day I`ve wrote :

=sum({<DATE={'$(=Date(max(DATE)-1)))'}>} Sales)

For the first expression I have a result but for the second expression it return 0 , even tough I have info in my database. Where is the problem?

Thank you

sunny_talwar · ‎2016-09-26

I think this is part of the issue

You don't have data from one day ago, you are trying to go one month back. If this is right, then try this:

Sum({<DATE={"$(=Date(AddMonths(Max(DATE), -1)))"}>} SALES)

Make sure to use SALES instead of Sales

UPDATE: Also make sure to use Date() function in the script so that you don't have to use it on the front end of the application

if QvdCreateTime('SALES.qvd') then

SalesToday:

LOAD Date(DATE) as DATE,

ID_TYPE,

ID,

SALES

FROM [SALES.qvd] (qvd);

ELSE

SalesToday:

LOAD Date(DATE) as DATE,

ID_TYPE,

ID,

SALES

FROM

[SALES.xlsx]

(ooxml, embedded labels, table is Sheet1);

View solution in original post

trdandamudi · ‎2016-09-26

May be as below:

=sum({<DATE={'$(=max(DATE)-1))'}>} Sales)

puttemans · ‎2016-09-26

=sum({<DATE={'$(=max(DATE)-1)'}>} Sales) ?

Anil_Babu_Samineni · ‎2016-09-26

I hope you made a muistake

=sum({<DATE={'$(=max(DATE)-1))'}>} Sales)

And for the previous day I`ve wrote :

=sum({<DATE={'=$(max(DATE)-1))'}>} Sales)

OR

=sum({<DATE={'=$('=' & max(DATE)-1))'}>} Sales)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

jonathandienst · ‎2016-09-26

Are you making selections on a field other than DATE? You will need to override this for the yesterday figure:

=sum({<DATE={'$(=Date(max(DATE)-1)))'}, MONTH, YEAR>} Sales)

(Replace MONTH, YEAR with the field/s that you are making selections on)

Note that other selections may change Max(Date) and affect the ability of the yesterday expression and you may need to extend the selection overrides.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

razvan_brais · ‎2016-09-26

It doesn`t work none of the above expressions.

Jonathan , Date is the only selection. But when I use this: =sum({<DATE={'$(=Date(max(DATE)-1)))'}>} Sales)

it brings me the previous day , but the result of the sum is 0.

Anil_Babu_Samineni · ‎2016-09-26

I don't think so, would your provide sample

=sum({<DATE={'=$(=Date(max(DATE)-1,'DateFormatHere')))'}>} Sales)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

puttemans · ‎2016-09-26

Are you sure to have sales for the previous day?

Please beware of the ')', as far as I can see, only my suggestion has an equal '(' and ')'.

Regards,

Clever_Anjos · ‎2016-09-26

Please try

=sum({<DATE={'$(=Date(max(DATE,2)))'}>} Sales)

sunny_talwar · ‎2016-09-26

I think you need to make sure that your Date format is correct. Does using Date() function give you the same format as DATE field. If it doesn't , then you will need to give the date format as pointed out by Anil.