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

Help with Addmonths script

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

5 Replies
sunny_talwar

May be just this

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

berndjaegle
Creator II
Creator II

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:

2018-07-26 11_46_43-Test_bj - Qlik Sense.jpg

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

    ];

2018-07-26 11_46_43-Test_bj - Qlik Sense2.jpg

BR,

Bernd

Anonymous
Not applicable
Author

Wow thanks

Anonymous
Not applicable
Author

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?

sunny_talwar

May be do this

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