Comparing WTD results with Previous WTD results

[JamGardner]

Hi All, 

New to Qlik and I am trying to compare a current weeks set of results to the previous weeks set of results either as a straight one away from the other or a % difference. 

I currently achieve the WTD results by using this expression 

Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(WeekStart(Max(Date))))<=$(=Date(Max(Date)))"}>}  Contacts)

its probably something really simple but I am struggling. 

Many Thanks

[PrashantSangle · Accepted solution]

try below 

for current week set

Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(WeekStart(Max(Date))))<=$(=Date(Max(Date)))"}>}  Contacts)

for previous week set

Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(WeekStart(Max(Date)-7)))<=$(=Date(Max(Date)-7))"}>}  Contacts)

Regards,

[JamGardner · Accepted solution]

Very helpful...and simple! 

Thanks

[JamGardner]

Hi - Hoping you can help again - is there a way to combine both so that they show on a single table - my client would like to see both on a single table and there are multiple measures to view? I tried to combine the two but it worked for one parameter but the second seemed to give cumulative results.  Thanks again.

Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(Week Start (Max(Date))))<=$(=Date(Max(Date)))"}>} Handled)
&
Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(Week Start(Max(Date)-7)))<=$(=Date(Max(Date)-7))"}>}  Handled)

[OmarBenSalem]

The topic has been answered, but I would want to leave this here to explain this in simpler words : 

set expressions seems difficult at first sight, but then you'll love working with them.

Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

How we do that?

Suppose our measure is : sum(Sales)

1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

We add these to force Qlik to not take into consideration our selection of date for example.

Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

To prohibit this, we must write the date=.

2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

So we're working with the field :

a) date={    }

b) Now we wanna this date to be <=selected date which is max(date) ;

max(date) is a function so it needs an "=" sign:

=max(date)

when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

Now we add the <= so we'll have :  <=$(=max(date) )

for the second part, we want our date to be >=01/01/2016 which is the start of the year:

a) same approach, we use the YearStart function that returns the start of the selected year: >=$(=YearStart(Max(date)))

Now our expression is : from : date={    }

to : date={>=$(=YearStart(Max(date)))<=$(=Max(date))}

Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

Year={2016}

If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

Country={'Tunisia'}

In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

Final expression for YTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

Same approach for MTD:

sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)

f we wanna focus on the YTD-1, we wanna alter this part:

DATE={">=$(=YearStart(Max(DATE)))<=$(=Max(DATE))"}

to do so, we want to situate our self in the previous year, to do that, their is a function called addYears.

How we use it? addYears('04/12/2017',-1) = 04/12/2016;

So our expression will become:

DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}

With this, if the max(Date) in 2017 is 24/03/2017 (like in your case)

The YTD-1 will return the NET_AMOUNT from 01/01/2016 to 24/03/2016 .

See the attached app

You'll find YTD, YTD-1

MTD, MTD-1

Your max(DATE) = 24/03/2017

Create a filter date, try to change your date and see how your expression will change to become from the year/month start to the selected date.

As of your expression using vCurrentYear, when you select a date, it wil return the Net Amount of only the selected date