Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

May be like:

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

View solution in original post

7 Replies
tresesco
MVP
MVP

May be like:

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

datanibbler
Champion
Champion

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

paulyeo11
Master
Master
Author

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

tresesco
MVP
MVP

Try a simpler one:

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

paulyeo11
Master
Master
Author

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

tresesco
MVP
MVP

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,

paulyeo11
Master
Master
Author

Hi Tres

Wow the way you understand SET is very supper good.

Paul