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

[#1]

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

[MK_QSL]

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

[#3]

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

[MK_QSL]

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

[#5]

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

[MK_QSL · Accepted solution]

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…

[#7]

hi manojqlik,

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