Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a stack bar chart with YTD, MTD, WTD, Prior MTD and Prior YTD,
So i was thinking to create the above calculations in the script level and use this in the chart as dimension and it expression simple use =sum(Sales) .
I just saw the above post of Johnw, who explain very well how to create these dimensions, I am following his logic to create these however struggling to create previous MTD and previous YTD.
here is my script;
Dates:
load Distinct [Order Date] as Date
Resident Fact;
AsOf:
load
"Date" as "As of Date",
'WTD' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while week("Date")=week("Date" -IterNo()+1) AND year("Date")=year("Date" -IterNo()+1);
Concatenate(AsOf)
LOAD
"Date" as "As of Date",
'MTD' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while month("Date")=month("Date" -IterNo()+1);
//MTD-1 Calculation
Concatenate(AsOf)
LOAD
"Date" as "As of Date",
'MTD-1' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while MonthName("Date")-1=MonthName("Date" -IterNo()-1);
Concatenate(AsOf)
load
"Date" as "As of Date",
'YTD' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while year("Date")=year("Date" -IterNo()+1);
//YTD-1 Calculation
Concatenate(AsOf)
load
"Date" as "As of Date",
'YTD -1' as "Date Range",
//MonthName("Date" -iterno()-11) as "Date"
date("Date" -iterno()+1) as "Date"
Resident Dates
while year("Date")-1=year("Date" -IterNo()+1);
Drop Table Dates;
In "Date Range" field i am only getting "MTD",'WTD' and "YTD" .
Please advise.
Hi,
I am able to do it using the following code in script;
//MTD-1 Calculation
Concatenate(AsOf)
LOAD
"Date" as "As of Date",
'[MTD-1]' as "Date Range",
MonthName(Date,-1)as "Date"
Resident Dates;
//YTD-1 Calculation
Concatenate(AsOf)
load
"Date" as "As of Date",
'[YTD -1]' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while year("Date")-1=year(MonthName("Date",-IterNo()-11));
Hi ,
Please find the image of the chart i am trying to create. it should have YTD/MTD/Previous YTD and Previous MTD.
Hi,
I am able to do it using the following code in script;
//MTD-1 Calculation
Concatenate(AsOf)
LOAD
"Date" as "As of Date",
'[MTD-1]' as "Date Range",
MonthName(Date,-1)as "Date"
Resident Dates;
//YTD-1 Calculation
Concatenate(AsOf)
load
"Date" as "As of Date",
'[YTD -1]' as "Date Range",
date("Date" -iterno()+1) as "Date"
Resident Dates
while year("Date")-1=year(MonthName("Date",-IterNo()-11));
Hi Ashish,
can you please copy entire script that will helpful for me.
Thanks
Vikas