Getting my straight table to match up to slider parameters

[#1]

Hi Qlik Community,

Having issues with my slider and was hoping someone could help out.

I have a slider with the following expression:

=aggr(sum({1} tran_amt), id)

My understanding is that it is supposed to aggregate the transaction amount based on the ID.

When I make the following selection in the slider: $1,040min and $1,526 max

I get a table with the right max (or so I think)

  but the minimum goes below $1,040:

Can someone please explain to me what I'm doing wrong? I've been having the same issue with other tables. The max seems to be taken into account but the min is not.

The table dimension is set to the ID and the expression reads sum(tran_amt) .

THANKS SO MUCH!

[Anonymous]

Mike,

Given the expression in slider, what you are actually selecting is the ID that has sum(tran_amt) when no selections made.  What you see I n the table is the list of these IDs which is, I'm sure, correct.  The amounts here could be smaller if any selections are made.

Regards,

Michael

[#3]

Thanks for the reply but I don't follow... if I wanted to select all the customers that had a sum(spend) between $1,040 and $1,526 how would I do that using the slider object? Each customer has an ID so I aggregated it by ID.

I didn't make any selections aside from that one slider selection. That's why I find this so bizarre. But even if I make other selections, why would the total go below what I set in the slider?

[Anonymous]

If you don't make any other selections, it should match.  Can you upload the app?

See if there are selections in some hidden fields (?)

If you do make selections...  Here is an example:

Let say you have an ID=XYZ with sum(tran_amt), with no selections, equal 100.  You make slider selection, values from 80 to 500, so this XYZ is selected by slider.  Now, another selection is made in an associated table, so the sum(tran_amt) turns to 30.  It is below 80 but it doesn't exclude XYZ, it just reduces the value of the sum.  It also explains that the values in table can't be above slider's max - selections can only reduce the sum, not increase it.

[jok]

One way to handle this is to create a table that summarizes the dollar values by ID so that you don't have to do that on the fly in the formula.  Your slider would then use the summarized $ amount to return the ID's that fall within that $ range.

[#6]

Unfortunately I can't upload the app because the data/layout is supposed to be confidential, but I did follow your steps and still had the same issue.

I cleared everything (using the green CLEAR ALL) , made the selection in the slider, and am still seeing a very small handful of items that do not fit the slider criteria, Could it be due to rounding errors?

99% of the selections in the app are list boxes, though. Why would picking the state a resident lives in affect the total(spend) parameters? Or picking the address type (customer/business) or whether they are a first time customer or not.

I really appreciate your help! We're actually hopping on a call with Qlikview in a week to get a little more insight into this. But should I expect for selections in list boxes will alter the minimum and maximum of the slider? And do you have any other suggestions on making this work aside from the slider without having to select all the spend values.

Thanks so much!

[#7]

Hi John,

Just want to clarify how this works. The database has over half a million customers so selecting the range in the table seems like it would be a challenge. Thanks!

[jok]

Not sure I follow you.  The slider would point directly to the summarized dollar field.  As the slider is moved, it will narrow the returned ID's that have summarized dollar amounts within the specified range.  It seems that this would scale much better than trying to perform a calculation on the fly within the slider control.

Attached is a sample to demonstrate what I'm thinking.

[#9]

That's exactly what I had to begin with, if you look at my very first post. My problem was that even though I selected a specific range within the slider tool, I was getting IDs with total spend values that were not part of the slider range I selected.

[jok]

If you already have one row per ID, then you don't need the AGGR function.  Just use the dollar field as the Data Field and not the AGGR function.