topic Re: Addmonths in script in QlikView

Addmonths in script

Mon, 12 Oct 2015 09:30:15 GMT

Hi,

I need to create transactions for a time period in a load script where I adding months within a time interval.

For example an action will happen every fourth month within the period 1.1.2014-30.6.2015 then I will need transactions with the following dates:

1.1.2014

1.5.2014

1.9.2014

1.1.2015

1.5.2015

Thank's

Re: Addmonths in script

petter — Mon, 12 Oct 2015 09:37:48 GMT

Do you need to create rows with transactions?

Re: Addmonths in script

Anonymous — Mon, 12 Oct 2015 09:39:01 GMT

Hi,

By using your date field ,

you can create a flag in Script,

If(Date>= AddMonths(Today(), -3), 1)as Last3MonthFlag

and in the front end you call it like,

Last3MonthFlag={1}

for 3 months data in chart to be displayed.

I hope this helps you .

Thanks,

Bunny

Re: Addmonths in script

Mon, 12 Oct 2015 09:39:10 GMT

yes I need to create transaction to my fact table

Re: Addmonths in script

Mon, 12 Oct 2015 09:42:52 GMT

Thank's,
But I think this will not help since I need to calculate the dates for occurrence

Re: Addmonths in script

Anonymous — Mon, 12 Oct 2015 09:54:37 GMT

Dear peter ,

Please Help me in obtaining this Its working on my local System but not on Test Instance.

Pivot Table Current and Previous Using above‌

Thanks in Advance.

Bunny

Re: Addmonths in script

petter — Mon, 12 Oct 2015 10:16:11 GMT

Not sure if this is what you are looking for but this script will generate transactions for you within a timespan and one event for every 4 months. You can introduce a random factor to filter out events too:

//Function to calculate Months Difference in script
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

vStart = MakeDate( 2014, 1 , 1);
vEnd = MakeDate( 2015, 6 , 30 );

Months = Ceil( $(MonthDiff(vStart,vEnd)) / 4 );

TRANSACTIONS:
LOAD
  *
WHERE
  Rand()<0.5;   // Filter out randomly 50% of the events - if you want ALL set it to <1
LOAD
  TransID & '-' & IterNo() AS TransID,
  Date,
  'Event #' & IterNo() AS EventID
WHILE
  IterNo() < 10;   // Generate 9 events for each month that comes from below
LOAD
  RecNo() AS TransID,
  AddMonths( '$(vStart)' , (RecNo()-1)*4 ) AS Date
AUTOGENERATE
  $(Months);

Re: Addmonths in script

Mon, 12 Oct 2015 10:50:39 GMT

Thank you Petter

This solution will work 🙂