topic ytd and mtd?is this expressions correct or not because i am getting more confuse ? in QlikView

ytd and mtd?is this expressions correct or not because i am getting more confuse ?

Sun, 17 Aug 2014 04:58:02 GMT

YTD (Year-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”}, Year = {$(=Max(Year))},Quarter = , Month = >} Sales)

QTD (Quarter-To-Date) Sales:
- Sum({$<MonthID = {“<=$(=Max(MonthID))”}, QuarterID = {$(=Max(QuarterID))}, Year = , Quarter = , Month = >} Sales)

MTD (Month-To-Date) Sales:
- Sum({$<MonthID = {$(=Max(MonthID))},Year = ,Quarter = ,Month = >} Sales)

Previous Month Sales:

Sum({$<MonthID = {$(=Max(MonthID) – 1)}, Year = , Quarter = , Month = >} Sales)

Previous Quarter Sales:

Sum({$<QuarterID = {$(=Max(QuarterID) – 1)}, Year = , Quarter = , Month = >} Sales)

Sales for the same Month but Previous Year:

Sum({$<MonthID = {$(=Max(MonthID) – 12)}, Year = ,Quarter = ,Month = >} Sales)

Sales for same Quarter of the Previous Year:

Sum({$<QuarterID = {$(=Max(QuarterID) – 4)}, Year = , Quarter = , Month = >} Sales)

YTD Sales for Previous Year

Sum({$<MonthID = {“<=$(=Max(MonthID) – 12)”},Year = {$(=Max(Year) – 1)},

Quarter = , Month = >} Sales)

Sales for Rolling 12 Months:
Sum({$<MonthID = {“>=$(=Max(MonthID) – 11)<=$(=Max(MonthID))”}, Year = , Quarter = , Month = >} Sales)

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

MK_QSL — Sun, 17 Aug 2014 06:14:48 GMT

These are from QlikView 11 For Developers Book... Point in Time Reporting Chapter...

Here Each Month, Quarter is assigned with an MonthID and QuarterID starting from First to Last...

So when you say Year = Max(Year)... means max Year from the selected Year or Max Year of the database if user hasn't selected anything... The same case applied to MonthID = Max(MonthID) and QuarterID = Max(QuarterID)..

Let us know what is your confusion...

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

Sun, 17 Aug 2014 06:50:08 GMT

so MonthID = Max(MonthID) QuarterID = Max(QuarterID) Year = Max(Year)

if mention thease are all variables and using above expressions means its correct only right?

or if u have any other way of writing expression please tell me

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

MK_QSL — Sun, 17 Aug 2014 08:07:54 GMT

There are many ways to get MTD, YTD, QTD, WTD... What is your actual requirements? Let us have the sample data or the requirements you want....

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

Sun, 17 Aug 2014 08:47:20 GMT

my req is ytd,mtd,qtd,wtd pls give me correct expressions

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

MK_QSL — Sun, 17 Aug 2014 12:47:08 GMT

Consider that you have below dummy data….

Load
Date(Date) as Date,
Month(Date) as Month,
'Q'&Ceil(Month(Date)/3) as Quarter,
Sales
Inline
[
Date, Sales
01/01/2014, 100
10/01/2014, 1000
04/02/2014, 500
03/03/2014, 1200
15/03/2014, 130
25/03/2014, 200
01/04/2014, 180
21/04/2014, 300
15/05/2014, 200
20/05/2014, 100
11/06/2014, 400
12/07/2014, 220
20/07/2014, 450
01/08/2014, 600
15/08/2014, 200
17/08/2014, 220
25/08/2014, 10000
];

SUM({<Date = , Month =, Quarter = >}Sales)

will give you Total Sales 16000

SUM({<Date = , Month =, Quarter = , Date = {">=$(=YearStart(Today()))<=$(=Today())"}>}Sales)
will give you Total Sales For Current Year to Date

SUM({<Date = , Month =, Quarter = , Date = {">=$(=MonthStart(Today()))<=$(=Today())"}>}Sales)
will give you Total Sales for Current Quarter to Date

SUM({<Date = , Month =, Quarter = ,Date = {">=$(=QuarterStart(Today()))<=$(=Today())"}>}Sales)
Will give you Total Sales For Current Month to Date

The above YTD, QTD and MTD irrespective of Date, Month and Quarter selection as these fields are already bypassed…

Now what if you want User to select Date and YTD, QTD and MTD should be based on the User Selected Date…

You can Replace Today() with Max(Date) in above formulas.

Let me know if you still have any doubt…

Re: ytd and mtd?is this expressions correct or not because i am getting more confuse ?

Mon, 19 Jan 2015 13:38:41 GMT

hi manojqlik,

mw too I have the same problem....I apply the the expressions above but no values populated on the fields.