Calculate today and yesterday total of sales

[razvan_brais]

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

[jonathandienst]

And remember that the field named Date is not the same as the field named DATE.

[razvan_brais]

Somehow it still doesn`t work , but all the solutions presented above , brings the previous day but the sum still returns 0. Maybe I`m missing something , I will need to pay a little more attention to the syntax. Thank you all for your help.

[sunny_talwar]

So, this isn't working?

=Sum({<DATE={"$(=Date(max(DATE)-1), 'DateFieldFormatHere'))"}>} Sales)

[razvan_brais]

Nope , doesn`t work. Should I convert and the first DATE field? How can I do that. What I can`t understand is why my expression for max(DATE) is working but the max(DATE)-1 isn`t.

[sunny_talwar]

Would you be able to share a small sample where you can demonstrate what is going on?

[trdandamudi]

See if the below works for you:

[razvan_brais]

Unfortunatly it still doesn`t work. I don`t know what else to do. I have attached my qvw example and both xls files. Hope one of you can help me.

Thank you.

[sunny_talwar · Accepted solution]

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);

[razvan_brais]

Yes , this works , yeeeeey. Thanks a lot . In my app I upload data from DB in which I have Date Column , so there is no need to cast to date. But thanks for the hint. Can you please look at the other Discussion that I started ? The link is bellow : Pivot Table suppress null values . Maybe you have an idea on this too.

Thanks again

[razvan_brais]

Hello , me again. I have an issue. When I want to calculate sum for previous day I would like to jump over weekends. So if today is Monday , I would like to set previous day as Friday. Is this possible?

Thank.