topic Re: Calculate Last 6 months in Load Script? in QlikView

Calculate Last 6 months in Load Script?

dmohanty — Tue, 28 Apr 2015 21:00:17 GMT

Hi All,

I have a Month field with 12 months values like = Mar-15, Feb-15, Jan-15, Dec-14, Nov-14 etc.

I want to write a script in Load statement, where it would calculate the Last 6 Months of data dynamically.

Ex- When I select Mar-15, then 6 Months of data(Mar-15 - Oct-14) will be calculated and will be displayed in an Object.

When I select Feb-15, then 6 Months of data(Feb-15 - Sep-14) will be calculated and will be displayed in an Object.

I tried using the below clause, but its getting calculated for the particular month (Its not becoming dynamic) :

[Month] >= addmonths(date(MonthName(Today(),0), 'MM/DD/YYYY'),-6)
and
[Month] < addmonths(date(MonthName(Today(),0), 'MM/DD/YYYY'),0)

So this expression is always calculating the data of latest month and previous 6 months.

Could someone please help this to make dynamic.

Regards!

Re: Calculate Last 6 months in Load Script?

ramoncova06 — Tue, 28 Apr 2015 21:18:52 GMT

your backend script does not has a direct relation to the data that gets displayed

QlikView App: Dates, Date Ranges and Set Analysis

here is an option to do this with set analysis

Re: Calculate Last 6 months in Load Script?

Tue, 28 Apr 2015 21:40:31 GMT

try this, hope this will help you.

suppose you have to calculate sale for last 6 month

if(

datefield >= addmonths(date(datefield),-6)
and
datefield < date(datefield),sales)

Re: Calculate Last 6 months in Load Script?

maxgro — Tue, 28 Apr 2015 22:00:14 GMT

PFA

Re: Calculate Last 6 months in Load Script?

dmohanty — Tue, 28 Apr 2015 22:00:57 GMT

My requirement was to make that calculation dynamic in Load script itself.

Don't we have any other solutions/options?

Re: Calculate Last 6 months in Load Script?

dmohanty — Tue, 28 Apr 2015 22:02:41 GMT

Thank you Massimo.

Can the same functionality be done in Load Script itself - by any means????

Re: Calculate Last 6 months in Load Script?

ramoncova06 — Tue, 28 Apr 2015 22:07:15 GMT

you can create several dynamic stuff in your script but this is not one of them, whatever you do in your script does not have direct relation with whatever you display in your objects.

maybe the requirement was that in the backend ? if that is the case then I believe Massimo already provided an alternative on to what to do.

I wouldn't go with IF statements to run this since they tend to be slower that doing this at set analysis level

Re: Calculate Last 6 months in Load Script?

maxgro — Tue, 28 Apr 2015 22:28:18 GMT

in the script you can cumulate for every

dim1, yearmonth

the sum of the same dim1, yearmonth-6 +.......+ yearmonth-1 + yearmonth

in the attachment my idea, try to see (and check) if it helps you

but I really don't understand why you want it in the script

Re: Calculate Last 6 months in Load Script?

Anonymous — Thu, 16 Nov 2017 06:43:40 GMT

Your script is really helpful. But i need next three months from current monthyear.

i have made some changes. That is,

year(date)*12-month(date) as monthid, /* in script

expression is

=sum({$ <year=,month=,monthyear=,monthid={">$(=max(monthid)-3)<=$(=max(monthid))"}>}val)

From these changes i am getting three months but like this. Suppose i have selected dec15 so it is giving me jan14, feb14,dec15. but what i want is next two months along with current selection i.e in my case is dec15, however it is giving me previous two months. i want dec15, jan16, feb16. how can i get this?

Please help.

Thanks

Re: Calculate Last 6 months in Load Script?

shraddha_g — Thu, 16 Nov 2017 06:53:39 GMT

sum({$ <year=,month=,monthyear=,monthid={">$(=max(monthid))<=$(=max(monthid)+2)"}>}val)

Re: Calculate Last 6 months in Load Script?

Anonymous — Thu, 16 Nov 2017 07:21:31 GMT

by doing +2 in script it is giving me all of the monthyear that are in my field. i think some change would be required in script line

i have done subtraction in script line by which i am getting jan14,feb14

year(date)*12-month(date) as monthid, /* in script

and in an expression i have written 3 by which it is showing me three months

=sum({$ <year=,month=,monthyear=,monthid={">$(=max(monthid)-3)<=$(=max(monthid))"}>}val)

The original script is

year(date)*12+month(date) as monthid, /* in script addition done