Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator III

Calculated dimension for YTD, MTD,WTD

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/16...

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.

 

Labels (2)
1 Solution

Accepted Solutions
ashis
Creator III
Creator III
Author

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

View solution in original post

3 Replies
Shubham_Deshmukh
Specialist
Specialist

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

Hi ,

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

 

ashis
Creator III
Creator III
Author

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

View solution in original post