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

[ianwilson]

Hi

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

[sushil353]

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 )

[ianwilson]

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

[ianwilson]

Hi

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

[sunny_talwar · Accepted solution]

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)

[ianwilson]

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

[sunny_talwar]

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?

[ianwilson]

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.

[sunny_talwar]

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

[ianwilson]

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.