Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD / Point in Time / Variable Date

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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)

View solution in original post

7 Replies
MK_QSL
MVP
MVP

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)

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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

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

Not applicable
Author

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)

Not applicable
Author

Thanks again Manish.

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