YTD / Point in Time / Variable Date

[#1]

Hi all,

I have a question about point in time reporting/YTD/variable date. (if it's right to name so!)

I set up a dashboard with "traditional" KPI (for example, SALES YTD, OPEN ORDER, etc...).

The SALES YTD is calculated between all sales of current year (2015) and previous year YTD (obviously)

-> more or less... sum({<Date={"<=AddYears(Today(), -1)"}>} sales)

Then my boss asked me...I would like to know the "YESTERDAY SITUATION", which could means yesterday or 1 years ago...

How can I modify the expression?

Should I introduce a new variable, "Analysis Date" and recalculate all the date depending on that variable?

Or is there a better solution?

More generally, which is the best way to analyze the "past situation" with Qlikview?

I mean, if I have data until today (13 APR 2015), how can ignore part of them and have the situation updated on (for example) 13 MAR 2015?

Thanks in advance!!!

[MK_QSL · Accepted solution]

You need to use Max(Date) instead of Today().

Consider that you have Date Field and you also have Year, Quarter, Month, Week, MonthYear calculated from this Date field. You want YTD based on the Date selected by user..

If user select 01/03/2015, you want YTD 01/01/2015 to 01/03/2015 and LYTD 01/01/2014 to 01/03/2014... You can achieve this by below

YTD

SUM({$<Year=, Quarter=, Month=, Week=, MonthYear=, Date = {">=$(=YearStart(Max(Date)))<=$(=Max(Date))"}>}Sales)

LYTD

SUM({$<Year=, Quarter=, Month=, Week=, MonthYear=, Date = {">=$(=YearStart(Max(Date),-1))<=$(=AddYears(Max(Date),-1))"}>}Sales)

Selected Date

SUM({$<Year=, Quarter=, Month=, Week=, MonthYear=, Date = {"$(=Max(Date))"}>}Sales)

Last Year Same Date

SUM({$<Year=, Quarter=, Month=, Week=, MonthYear=, Date = {'$(=AddYears(Max(Date),-1))'}>}Sales)

[#3]

Hi,

For previous month from today calculate like below,

sum({<Date={"$(=addmonths(today(),-1))"}>} sales)

Please clarify on which range data you need to show in report ...

[#4]

Thanks Manish,

so you suggest to not use a variable but use the date selected by the user, right?

The last expression is LYTD on Selected Date, right?

Do you know any post or article which I could refer?

[#5]

Thanks Harshal,

actually I don't know in advance the data range.

I would like to give the possibility to user to define the date of the analysis

[MK_QSL]

You can create a variable and use them.. I generally use this set analysis without variable because you are not going to change the expression...

i.e. YTD and LYTD is going to remain the same based on Max(Date) selected by user, so there would be no difference if you use Variable or not...

[#7]

If it is the case you can calculate like below,

YTD

create variable : vMaxDate

-> sum( {<CalDate = {">=$(= Date (min( {<CalMonth=, CalYear=, CalQuarter=>} QuaterStartDate )))<=$(vMaxDate))" }, CalYear=, CalMonth=, CalQuarter= >} Sales))

-or-

Year to Year

sum({<CalDate = {">=$(=Date(Yearstart(max(CalDate))))<=$(=max(CalDate))"},CalMonth=,CalQuarter=>}Sales)

-or-

LY YTD

sum({<CalDate = {">=$(=date(yearstart(Max(CalDate),-1)))<=$(=Date(ceil(addyears(Max(CalDate)+1,-1))))"},CalYear=>}Sales)

[#8]

Thanks again Manish.

The expression "Year=, Quarter=, Month=, Week=, MonthYear=," is to ignore the selections made by the users in that fields, right?