Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

DateActive Products
3/16/20135,961
3/17/20135,202
3/18/20136,631
3/19/20136,084
3/20/20134,667
3/21/20135,338
3/22/20135,344
3/23/20136,076
3/24/20134,661
3/25/20136,631
3/26/20136,076
3/27/20133,922
3/28/20133,982
3/29/20135,942
3/30/20136,629
3/31/20135,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

1 Solution

Accepted Solutions
jeffmartins
Partner
Partner

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

View solution in original post

4 Replies
jeffmartins
Partner
Partner

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

Not applicable
Author

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
Partner
Partner

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

Not applicable
Author

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

larry