Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Year on year analysis but relative to arbitrary dates in each year

We have a data set that gives us daily energy readings over about 10 years. What the user would like to see is a line chart with a line for each year. But the difficulty is they would like to see readings relative to arbitrary dates in each year. For example, they might want to select Good Friday in each year then see the energy readings for 10 day either side of that. So I'm imagining the Good Fridays would be  'Day zero' on the x-axis and days -10 to +10 either side of that (but the user could choose the window size). I'm struggling to think how I can create a window of -10 to +10 days (say) based on one date in each year.

Yes, I could set up a table of special dates and include these when loading I want the user to be able to pick any day they would like from each of the years.

Any suggestions gratefully received.

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Year on year analysis but relative to arbitrary dates in each year

Or create an alternate state for each year. Create a listbox for each year and assign it to the appropriate alternate state. Create an expression in your line chart for each year using the alternate state for the set identifier:

     sum({AltStateYear1<MyDate={">=$(=only({AltStateYear1}MyDate-10))<=$(=only({AltStateYear1}MyDate+10))"}>} Reading)

     sum({AltStateYear2<MyDate={">=$(=only({AltStateYear2}MyDate-10))<=$(=only({AltStateYear2}MyDate+10))"}>} Reading)

     ....etc

     sum({AltStateYear3<MyDate={">=$(=only({AltStateYear3}MyDate-10))<=$(=only({AltStateYear3}MyDate+10))"}>} Reading)


talk is cheap, supply exceeds demand
Highlighted
Creator
Creator

Re: Year on year analysis but relative to arbitrary dates in each year

Gysbert, thanks for the reply. Alternative states is something I haven't tried before - looks promising. Let me try that out tomorrow when I am a bit more awake!

Highlighted
Creator
Creator

Re: Year on year analysis but relative to arbitrary dates in each year

Thanks again for your help.

Just to feedback I succeeded in getting the alternate states to work, showing periods on my chart. Where I'm failing is the final step of overlaying the lines on the same part of the chart, in other words having the date I picked in each of the alternate states as 0 on the x-axis of -10 to +10 and and my lines relative to that.

For now I'm loading pre-defined periods which works OK but is in inflexible of course.