topic Re: Single date selection - date and same day last year in QlikView

Single date selection - date and same day last year

nancymarmat — Fri, 13 May 2016 15:26:28 GMT

I have 3 date selection boxes: Year, Month and Mfg Date. They are all based on a single field, MfgDate.

My pivot table contains data for Current Year and Prior Year.

sum({$< Year={$(=Only(Year))}>} Yards)

sum({$< Year={$(=Only(Year)-1)}>} Yards)

This works when I select Year and Month. However when I select an individual day in the Mfg Date selection box, I lose the data for Prior Year.

How can I override the year and month if I have made a selection for Mfg Date?

When I make a selection in Mfg Date, I would like the selected date plus the same day last year automatically. It will display both dates if I select them both from the list. Is it possible in Qlikview to make one date selection and in the expression use the same day for the prior year?

Thank you for any ideas.

Re: Single date selection - date and same day last year

settu_periasamy — Fri, 13 May 2016 16:25:02 GMT

May be try like this..

=sum({$< Year={"$(=Year(Addyears(MfgDate,-1)))"},Month>} Yards)

Re: Single date selection - date and same day last year

nancymarmat — Fri, 13 May 2016 17:29:47 GMT

It did not return the desired results.

It did point me in the right direction...=sum({$< Year={"$(=Year(Addyears(MfgDate,-1)))"},MfgDate>} Yards)

returns data for the entire month of the prior year., but the Year and Month selectors no longer work.

How do I use Addyears only if there is a selection in the MfgDate selection list?

Re: Single date selection - date and same day last year

nancymarmat — Fri, 13 May 2016 18:17:53 GMT

I used this workaround - I added the Day field to the script and changed the MfgDate selection to Day selection. When the three selections, Year, Month and Day are used together the existing expressions work perfectly.

Thank you for your suggestions. I learned about a new function Addyears which will be very helpful in the future.