topic Re: inyearTodate in chart expression in QlikView

inyearTodate in chart expression

Tue, 16 Oct 2012 22:48:03 GMT

Hi all

I have a few different bar charts that follow different expressions. By default I want the expression to choose the highest year

Prior Year:

Sum({<InvoiceYear={$(=Max(InvoiceYear)-1)}>} (SBQSHP * Price * FACTOR))

Works fine.

Year to date:

Sum(if(InYearToDate([Invoice Date], MakeDate($(=Max(InvoiceYear)), Month(Today()), Day(Today())), 0), Sales))

Also works fine, but now I want prior year to date and whenever I select a year the prior YTD chart goes blank. If I select more than one year it will fill it correctly based on the second highest year.

Prior Year to Date:

Sum(if(InYearToDate([Invoice Date], MakeDate($(=Max(InvoiceYear)), Month(Today()), Day(Today())), -1), Sales))

Sum(if(InYearToDate([Invoice Date], MakeDate($(=Max(InvoiceYear)-1), Month(Today()), Day(Today())), 0), Sales))

I have tried both of these and they yield the same result. I feel like I am missing something obvious but I'm not seeing it

Thanks

Re: inyearTodate in chart expression

thomas_skariah — Wed, 17 Oct 2012 01:26:17 GMT

Hi,

You can get an idea from the below link.

http://community.qlik.com/message/253866#253866

Regards,

Tom

Re: inyearTodate in chart expression

Gysbert_Wassenaar — Wed, 17 Oct 2012 06:50:01 GMT

try this:

sum({<InvoiceYear={$(=Only(InvoiceYear)-1)}, InvoiceDate={'<=$(=MakeDate(Year(InvoiceYear)-1, Month(today()), Day(today()))'}>} (SBQSHP * Price * FACTOR))

Re: inyearTodate in chart expression

Wed, 17 Oct 2012 16:59:01 GMT

It didn't work, I think you are one parenthesis short after Day(today()

It still only shows under certain multiple selection circumstances

Ex:

Year selected

-nothing- shows prior year to 2012 (2011)

2012 shows nothing

2012, 2010 shows nothing

2012, 2011 shows prior year to 2012(2011)

Re: inyearTodate in chart expression

Wed, 17 Oct 2012 20:59:30 GMT

I found a solution based off of your solution.

Thanks!