Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like:
If([date] >= MonthStart(Today(),-5) and [date] < $(vMonthNow), -1, 0) As YTD,
May be like:
If([date] >= MonthStart(Today(),-5) and [date] < $(vMonthNow), -1, 0) As YTD,
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
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
Try a simpler one:
If([date] >= MonthStart(Today()) and [date] < $(vMonthNow), -1, 0) As L0M,
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
Because your vMonthNow variable is not holding today's date but the MonthStart of this month. Redefine your variable like:
Let vMonthNow = Today();
Instead of
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,
Hi Tres
Wow the way you understand SET is very supper good.
Paul