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

Addmonths in script

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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

Do you need to create rows with transactions?

Anonymous
Not applicable
Author

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

Not applicable
Author

yes  I need to create transaction to my fact table

Not applicable
Author

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

Anonymous
Not applicable
Author

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

petter
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

Thank you Petter

This solution will work 🙂