Sum sales between dates

[#1]

Hello,

Here's my doubt,

Selecting a date (DD/MM/YYYY) I need to get the sum of the sales of the previous 8 days (from DD/MM/YYYY-8 to DD/MM/YYYY-1), is it possible?

Thank you!

[#2]

Hi there,

Try something similar to this (not tested):

sum({<Date = {">=$(=AddDays(only(Date), -8))<=$(=AddDays(only(Date), -1))"} >} Sales)

Probably you would have to do some casting, I'm not really sure, since I haven't tested, if something goes wrong, try something similar to this:

sum({<Date = {">=$(#=AddDays(only(Date), -8))<=$(#=AddDays(only(Date), -1))"} >} Sales)

Regards

[#3]

Hello Iván,

It doesn't work...is the expression adddays correct? I don`t see it in the QVW Help,

I'll keep trying,

Regards

[johnw]

Dates are stored in days, so to subtract days, just use "-", then reformat as a date. Untested, but:

sum({<Date={">=$(=date(only(Date)-8)) <$(=only(Date))"}>} Sales)

[#5]

It worked, thank you very much!

[Anonymous]

You can also experiment with using MAX(date) instead of ONLY(date) this way the user does not have to make a selection to view the latest data.

There will be data shown when the user has no selections made. With the only() qualifier you need to make one distinct selection on the date column.

Cheers.

Ad.

[#7]

Hello,

Selecting a month and using this kind of expression:

sum({<Month={"$(=date(only(Date)-8))"}>} Sales)

Is it possible to show the result for all days in a month?? I have not tested yet...

regards

[#8]

Hi there, you can use the following expression together with Date dimension. You will have to clear at least Month and Year user selections, because of the last seven days of each month will actually belong to the previous month when you subtract the eight days, in the case of the first seven days of january, will belong no only to the previous month but also the previous year.

Your expression should look similar to the following:

sum({<Date={">=$(=date(max(Date)-8)) <$(=max(Date))"}, Month=, Year=>} Sales)

Regards

[#9]

Hi Ivan,

I thought I could use this post to explain the problem that I have now but maybe I was wrong,

here is my problem:

Selecting a month and using this expression :

SUM({<DAY={"$(=timestamp(Addmonths(only(DAY),-1)))"}NET_PRICE)

/ SUM({<DAY={"$(=timestamp(addyears(Addmonths(only(DAY),-1),-1)))"},STATUS={'CONTRACT'},IS_MERCHANT={'Y'}>}NET_PRICE)*

SUM({<DAY={"$(=timestamp(addyears(only(DAY),-1)))"},STATUS={'CONTRACT'},IS_MERCHANT={'Y'}>}NET_PRICE)

How can I show the result for each day of the month selected??

I need the month selection to show the days in this month...

If I use the DAY dimension returns null because the days from the selected month don't match with the days setted in the expression above,

Any Idea?

I think it's better explained this time,

Thank you!