Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

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
Not applicable

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,

7 Replies
tresesco
Not applicable

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,

datanibbler
Not applicable

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

paulyeo11
Not applicable

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

tresesco
Not applicable

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,

paulyeo11
Not applicable

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

tresesco
Not applicable

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();

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
Not applicable

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