Getting the values for the last and next to last dates.

[#1]

Greetings,

Very new to qlickview (a matter of days actually), and so far so good. However I'm having an issue trying to get the value the last record. I am using a simple bar chart with Date on the x axis and a product count for the Y axis.

What I need to do is get the product count (an integer value) for the last day, then compare it to the second to last day Here's what the table looks like:

Date	Active Products
3/16/2013	5,961
3/17/2013	5,202
3/18/2013	6,631
3/19/2013	6,084
3/20/2013	4,667
3/21/2013	5,338
3/22/2013	5,344
3/23/2013	6,076
3/24/2013	4,661
3/25/2013	6,631
3/26/2013	6,076
3/27/2013	3,922
3/28/2013	3,982
3/29/2013	5,942
3/30/2013	6,629
3/31/2013	5,824

What I need to get is the Active Products values for 3/31/2013 (for example) and 3/30/2013

Any help or suggestions on how to solve this would be most appreciated.

Many thanks,

larry

[jeffmartins · Accepted solution]

Hi larryclyons,

You can try rank() and aggr() functions in the bar chart expression to solve this issue.

Your expression would be as follow: if( aggr(rank(sum({1}Date)), Date) <= 2, Sum([Active Products]))

See the attached file. Hope this helps you.

Regards

[#3]

Thanks Jeff your help here is very appreciated.

As I mentioned I'm a complete newbie, and don't necessarily understand how the aggr functions work. How would I get these two just as numbers or variables? The requirement is to display the last day's numbers and the difference between the last day and the next to last day. (as in last day's sum of Active Products minus the next to last day's sum of Active Products).

Many thanks,

larry

[jeffmartins]

Hi larryclyons,

Try to write two expressions one for the last day's number and other for the difference between the last day's number and the next to last day.

See the attached file. Hope it helps you.

Regards

[#5]

Thanks again. You've been very helpful. That worked.

larry