topic Sum sales between dates in QlikView

Sum sales between dates

Thu, 14 Oct 2010 15:41:05 GMT

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!

Sum sales between dates

Thu, 14 Oct 2010 16:18:11 GMT

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

Sum sales between dates

Thu, 14 Oct 2010 19:22:39 GMT

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

Sum sales between dates

johnw — Thu, 14 Oct 2010 19:32:37 GMT

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)

Sum sales between dates

Thu, 14 Oct 2010 19:44:01 GMT

It worked, thank you very much!

Sum sales between dates

Anonymous — Fri, 15 Oct 2010 16:05:37 GMT

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.

Sum sales between dates

Wed, 26 Jan 2011 14:03:19 GMT

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

Sum sales between dates

Wed, 26 Jan 2011 16:43:03 GMT

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

Sum sales between dates

Wed, 26 Jan 2011 18:55:09 GMT

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!