Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
You can get an idea from the below link.
http://community.qlik.com/message/253866#253866
Regards,
Tom
try this:
sum({<InvoiceYear={$(=Only(InvoiceYear)-1)}, InvoiceDate={'<=$(=MakeDate(Year(InvoiceYear)-1, Month(today()), Day(today()))'}>} (SBQSHP * Price * FACTOR))
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)
I found a solution based off of your solution.
Thanks!