Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be just this
Date(MakeDate(Year(Date#(Year, 'YYYY')), Month))
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
Wow thanks
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?
May be do this
Date(MakeDate(Year(Date#(Year, 'YYYY')), If(Month > 10, Month-9, Month+3)))