topic Re: Help with Addmonths script in QlikView

Help with Addmonths script

Anonymous — Wed, 25 Jul 2018 10:47:47 GMT

I've been tasked with amending a dashboard and have found the following script:

addmonths((Date#(Year, 'YYYY')), (right(Quarter, 1) * 4) - right(Quarter, 1)) as KPIDate // start date is 01/01 of the year, addmonths to get the right quarter

I've gathered that this is using todays date and setting the KPI Date to the current quarter.

The dashboard is actually reporting on quarterly data and using quarterly data from QVDs.

Some developers have now created monthly versions of the same QVDs and would like me to create a monthly tab on QV.

There are many changes I need to make, however with regards to the Addmonths script, will changing it to the following give me the current month?

It would be really great understand what this script is actually doing

addmonths((Date#(Year, 'YYYY')), (right(Month, 1) * 12) - right(Month, 1)) as KPIDate // start date is 01/01 of the year, addmonths to get the right month

Re: Help with Addmonths script

sunny_talwar — Wed, 25 Jul 2018 19:32:19 GMT

May be just this

Date(MakeDate(Year(Date#(Year, 'YYYY')), Month))

Re: Help with Addmonths script

berndjaegle — Thu, 26 Jul 2018 10:02:29 GMT

Hi Nick ,

Check this little snippet to check what your sript does.

Table:

Load *,

Date(addmonths((Date#(Year, 'YYYY')), (right(Quarter, 1) * 12) - right(Quarter, 1)),'DD.MM.YYYY') as KPIDate

;

Load * inline

[

Quarter,Year

Q1,2018

Q2,2018

Q3,2018

Q4,2018

];

Result:

What you need is:

Table: // this you load from your Table. I use inline instead as I don't have your data.

Load * inline

[

Quarter,Year

Q1,2018

Q2,2018

Q3,2018

Q4,2018

];

join(Table) // this simple enhances your table. But consider that you probably multiply your data.

Load * inline

[

Quarter,Month

Q1,1

Q1,2

Q1,3

Q2,4

Q2,5

Q2,6

Q3,7

Q3,8

Q3,9

Q4,10

Q4,11

Q4,12

];

BR,

Bernd

Re: Help with Addmonths script

Anonymous — Thu, 26 Jul 2018 13:37:43 GMT

Wow thanks

Re: Help with Addmonths script

Anonymous — Mon, 30 Jul 2018 13:28:49 GMT

Hi Sunny

So I have a table with 12 columns for months ie the "Month" field contains values from 1 to 12, however Month 1 is actually April as our Fiscal year starts in April and your code above returns 1/1/2018.

I tried putting in Month+3 but didnt work.

Also not sure how it would handle month 10 which should be Jan 2019?

Re: Help with Addmonths script

sunny_talwar — Mon, 30 Jul 2018 13:38:51 GMT

May be do this

Date(MakeDate(Year(Date#(Year, 'YYYY')), If(Month > 10, Month-9, Month+3)))