topic Re: Calculated dimension for YTD, MTD,WTD in QlikView

Calculated dimension for YTD, MTD,WTD

ashis — Sat, 16 Nov 2024 03:24:15 GMT

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

https://community.qlik.com/t5/QlikView-App-Development/Calculated-dimension-for-year-to-date/td-p/166510

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.

Re: Calculated dimension for YTD, MTD,WTD

Shubham_Deshmukh — Tue, 04 Jun 2019 08:32:36 GMT

Can u ecplain exact requirement? you want MTD YTD on X axis and sum(Sales according to that ??

Re: Calculated dimension for YTD, MTD,WTD

ashis — Tue, 04 Jun 2019 09:03:25 GMT

Hi ,

Please find the image of the chart i am trying to create. it should have YTD/MTD/Previous YTD and Previous MTD.

Re: Calculated dimension for YTD, MTD,WTD

ashis — Thu, 06 Jun 2019 03:34:06 GMT

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

Re: Calculated dimension for YTD, MTD,WTD

vikasmahajan — Mon, 27 Jun 2022 07:20:38 GMT

Hi Ashish,

can you please copy entire script that will helpful for me.

Thanks

Vikas