Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
