7 Replies Latest reply: Sep 21, 2015 3:52 AM by Yeo Poh sai

# Possible to compute last 5 month sales with out using partial load  ?

Hi All

I want to compute last 5 month sales.

Now i have YTD script which give Year To Date sales :-

If(Num([date]) >= \$(vYearStart) and Num([date]) < \$(vMonthNow), -1, 0) As YTD,

May i know how to modify the above script , so that it will return last 5 month sales amount.

Hope some one can advise me ?

Paul

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

May be like:

If([date] >= MonthStart(Today(),-5) and [date] < \$(vMonthNow), -1, 0) As YTD,

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Hi Tres

Thank you very much for your expression , which is working fine :-

If([date] >= MonthStart(AddMonths(Today(),-1)) and [date] < \$(vMonthNow), -1, 0) As L1M,

If([date] >= MonthStart(AddMonths(Today(),-2)) and [date] < \$(vMonthNow), -1, 0) As L2M,

when i try to get currect month sales amount , using below expression , it return zero :-

If([date] >= MonthStart(AddMonths(Today(),-0)) and [date] < \$(vMonthNow), -1, 0) As L0M,

Hope you can advise me ?

Paul

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Try a simpler one:

If([date] >= MonthStart(Today()) and [date] < \$(vMonthNow), -1, 0) As L0M,

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Hi Tres

When i try the below script :-

If([date] >= MonthStart(Today()) and [date] < \$(vMonthNow), -1, 0) As L0M,

it return zero.

Enclosed my QV Doc.

Paul

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Because your vMonthNow variable is not holding today's date but the MonthStart of this month. Redefine your variable like:

Let vMonthNow = Today();

Let vMonthNow = Num(MakeDate(Year(Today(1)), Month(Today(1)), 1));

OR,

use expression like:

If([date] >= MonthStart(Today()) and [date] <= Today(), -1, 0) As L0M,

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Hi Tres

Wow the way you understand SET is very supper good.

Paul

• ###### Re: Possible to compute last 5 month sales with out using partial load  ?

Hi paul,

it is a bit hard to understand just what you are doing without more context - that statement uses two variables.

But the function Monthstart(Today(), -5) will give you the first day of the month, five months back.

Can you go from there?

Best regards,

DataNibbler