topic Re: Same Weekday same Week Last Year as part of a range covering last 7 days in QlikView

Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Sun, 26 Jun 2016 23:32:48 GMT

I have a straight table with the following Dimension which displays data for the previous 7 days. varViewDate simply sets the "current date" of the data to look at:

=Date(if(Date>=($(varViewDate)-7) and Date<=($(varViewDate)-1), Date),'DD/MM/YYYY')

And expressions to display the corresponding revenue and budget for each of those days. These exclude any date fields from the expression so they only ever show the last 7 days:

=ONLY({$<Year=,Quarter=,Month=,Week=,Day=,Date={'>=$(=(varViewDate)-7)<=$(=(varViewDate)-1)'}>}Revenue)

=ONLY({$<Year=,Quarter=,Month=,Week=,Day=,Date={'>=$(=(varViewDate)-7)<=$(=(varViewDate)-1)'}>}RevBudget)

This all works correctly so far.

What I now want to do is display the data for the "same day" last year. "Same day" is the same day of the week in the same week of the year last year, across the range for the previous 7 days. For example Thursday 2nd June 2016 corresponds to Thursday 28th May 2015. Shown here as column "2015".

In a text box, the formula below displays the correct date last year. Changing the number 7 correctly modifies how many days before the "current day last year" is displayed:

=Date ( MakeWeekDate ( Year($(varViewDate))-1 , week($(varViewDate)-7) , weekday($(varViewDate)-7)) , 'DD/MM/YYYY' )

This needs to be incorporated in Set Analysis. I've tried many expressions, none of which display any data, I only get the Null() "-" character:

=ONLY({$<

Year=,Quarter=,Month=,Week=,Day=,Date=

>= $(=Date ( Makeweekdate (

Year($(varViewDate))-1 , Week($(varViewDate)-7) , Weekday($(varViewDate)-7)

)))

<= $(=Date ( Makeweekdate (

Year($(varViewDate))-1 , Week($(varViewDate)-1) , Weekday($(varViewDate)-1)

)))

>} Revenue )

I've also created a variable called SameDayLastYear and tried using that instead of the Makedate function, but without any joy.

I'm tempted to create a field in the load script to calculate the date, but hardcoding the dates for the previous year doesn’t work either:

=ONLY({$<Year=,Quarter=,Month=,Week=,Day=,Date={'>=$(=Date(42146))<=$(=Date(42152))'}>}Revenue)

I've seen similar examples on the community site which suggests this is possible. Working through other examples such as the following, I can't see why this doesn't work.

https://community.qlik.com/docs/DOC-9162

https://community.qlik.com/message/793168#793168

https://community.qlik.com/message/13511#13511

Help greatly appreciated.

Thanks

Ian

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

sushil353 — Mon, 27 Jun 2016 06:49:55 GMT

Try this:

=ONLY({$<

Year=,Quarter=,Month=,Week=,Day=,Date=

{">= $(=Date ( Makeweekdate (Year($(varViewDate))-1 , Week($(varViewDate)-7) , Weekday($(varViewDate)-7) ))) <=$(=Date ( Makeweekdate (Year($(varViewDate))-1 , Week($(varViewDate)-1) , Weekday($(varViewDate)-1))))"}>} Revenue )

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Mon, 27 Jun 2016 08:19:20 GMT

Hi Sushil

That doesn't work either. Double quotes also kill my table expressions if I use them. I've also tried the following as suggested in another post on the makeweekdate function, but this doesn't work:

=ONLY({$<

Year=,Quarter=,Month=,Week=,Day=,Date=

>= $(=Makeweekdate(

Year($(varViewDate))-1 , Week($(varViewDate)-7) , Weekday($(varViewDate)-7)

))

<= $(=Makeweekdate(

Year($(varViewDate))-1 , Week($(varViewDate)-1) , Weekday($(varViewDate)-1)

))

>} Revenue )

Thanks

Ian

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Tue, 28 Jun 2016 20:51:31 GMT

I've created a example ( based on https://community.qlik.com/message/803051#803051 from swuehl‌ - thanks for the base app ).

The only requirement is to populate the previous year column in the table on the first tab. Given the base code from swuehl‌ I don't understand why changing it in the way I have doesn't work.

Any help greatly appreciated!

Thanks

Ian

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

sunny_talwar — Tue, 28 Jun 2016 21:10:47 GMT

See if this is what you want (the chart on the right)

Dimension:

DEC_Date

Expressions:

1) Only({<DEC_Date = {">=$(=MakeWeekDate(Year(today()), Week(today()-7), WeekDay(today()-7)))<=$(=MakeWeekDate(Year(today()), Week(today()-1), WeekDay(today()-1)))"}>}Value)

2) Below(Only({<DEC_Date = {">=$(=MakeWeekDate(Year(today())-1, Week(today()-7), WeekDay(today()-7)))<=$(=MakeWeekDate(Year(today())-1, Week(today()-1), WeekDay(today()-1)))"}>}Value), 7)

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Tue, 28 Jun 2016 21:40:28 GMT

Hi Sunny

That is exactly what I need. It doesn't work in my original application, so I have some troubleshooting to do, probably on the calendar or the 10 other expressions I have - I'll strip it down and start with this expression first.

I've got the following, where varViewDate, just sets the current view date because my test data is a week out of date. Anything obviously wrong?

Dimension:

Date

Expressions:

Only({<Date = {">=$(=MakeWeekDate(Year($(varViewDate)), Week($(varViewDate)-7), WeekDay($(varViewDate)-7)))<=$(=MakeWeekDate(Year($(varViewDate))-1, Week($(varViewDate)-1), WeekDay($(varViewDate)-1)))"}>}Revenue)

Below(Only({<Date = {">=$(=MakeWeekDate(Year($(varViewDate))-1, Week($(varViewDate)-7), WeekDay($(varViewDate)-7)))<=$(=MakeWeekDate(Year($(varViewDate))-1, Week($(varViewDate)-1), WeekDay($(varViewDate)-1)))"}>}Revenue), 7)

I don't understand why below( , 7 ) works. Any chance you can explain why referring to a row 7 below the current works when that row would not relate to the date required?

Thanks very much for your help!

Ian

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

sunny_talwar — Wed, 29 Jun 2016 02:00:56 GMT

The idea is that if you remove the Below function here, there will be 14 rows in total

7 for this year and 7 for last year. In order to put the last year values in front of this year's value, I pulled them 7 rows up.

Does this make sense?

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Wed, 29 Jun 2016 07:27:15 GMT

Hi Sunny

That really does helps explain how Qlikview is using the Set Analysis to create a subset of the data in the Chart. I have done a lot of reading to try and solve this problem and not found that explained anywhere.

I haven't been able to get this working in my application yet. Being a newbie I am going to use this as a learning exercise to get this working with a small subset of my application and add things back in to find out what is wrong.

Thanks for the helps - appreciated.

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

sunny_talwar — Wed, 29 Jun 2016 15:50:52 GMT

Do you have another expression/s in your table you are viewing this in?

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

ianwilson — Wed, 29 Jun 2016 16:28:46 GMT

Hi Sunny

Yes, I have 11 expressions and have realised I will need to work out which row each of the expressions will appear on, plus be careful with the effect of Null results etc. On the plus side, they all either relate to the 7 days this year or the 7 days last year, so I should only get 14 rows and only need the below( ,7) for the expressions associated with last year.

With so many expressions is there a better way of doing this to achieve the same results?

On the plus side, I have not only got this working in my application, but it makes sense, so I'm a happy bunny.

Thanks for your help.

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

New-Qlik — Tue, 17 Mar 2020 08:20:23 GMT

Hello Sunny ,

I tried your solution n its worked nicely for TY week but for LY it works like this

Sales TY	Sales LY	Sales Order Purchase Date
0	0	3/13/2019
0	0	3/14/2019
0	0	3/15/2019
0	0	3/16/2019
0	0	3/17/2019
0	0	3/18/2019
0	0	3/19/2019
89	-	3/11/2020
67	-	3/12/2020
78	-	3/13/2020
38	-	3/14/2020
22	-	3/15/2020
31	-	3/16/2020
34	-	3/17/2020

n if i remove below condition from LY its like this

Sales TY	Sales - LY	Date
0	14	3/13/2019
0	11	3/14/2019
0	94	3/15/2019
0	16	3/16/2019
0	23	3/17/2019
0	11	3/18/2019
0	12	3/19/2019
89	0	3/11/2020
67	0	3/12/2020
78	0	3/13/2020
38	0	3/14/2020
22	0	3/15/2020
31	0	3/16/2020
34	0	3/17/2020

Can you help.i used this expression

sum({< Date = {">=$(=MakeWeekDate(Year(today())-1, Week(today()-7), WeekDay(today()-7)))<=$(=MakeWeekDate(Year(today())-1, Week(today()-1), WeekDay(today()-1)))"}>}[Sales])

Re: Same Weekday same Week Last Year as part of a range covering last 7 days

New-Qlik — Tue, 17 Mar 2020 08:32:58 GMT

@sunny_talwar Kindly suggest.