Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sum({$<MonthID = {“<=$(=Max(MonthID))”}, Year = {$(=Max(Year))},Quarter = , Month = >} Sales)
Quarter = , Month = >} Sales)
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…
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...
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
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....
my req is ytd,mtd,qtd,wtd pls give me correct expressions
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…
hi manojqlik,
mw too I have the same problem....I apply the the expressions above but no values populated on the fields.