topic Re: Creating a Percent Range for Selection in QlikView

Creating a Percent Range for Selection

benvatvandata — Fri, 12 Aug 2016 20:10:43 GMT

Hi,

I have a chart that looks something like this:

Customer ItemCode Customer Item Qty Customer Percent % Total Item Qty

1 1 100 1.01% 9,900

23 1 1,000 10.10% 9,900

2 2 12,400,000 74.22% 16,708,000

3 2 4,308,000 25.78% 16,708,000

4 5 8,840,000 50.02% 17,673,000

etc.....

Expressions:

Customer Item Qty: =SUM( OrderedQty )

Customer Percent %: = [Customer Item Qty] / [Total Item Qty]

Total Item Qty: =SUM( Total <ItemCode> OrderedQty )

I want the slider object to be a percent range 0% - 100% (1/4 intervals) that allows the user to filter the chart based on the range of percentages selected

For example:

0% 25% 50% 75% 100%

<-----|----------|----------|----------|----------|----->

If the user selects range 25% - 100% then only the last 3 rows of my example should display.

I have tried using max and min variables with set analysis, but haven't been able to figure it out.

Could someone please explain how to do this?

Thanks,

Ben V

Re: Creating a Percent Range for Selection

sunny_talwar — Fri, 12 Aug 2016 20:23:13 GMT

Check if the attached is what you are looking for...

Re: Creating a Percent Range for Selection

benvatvandata — Fri, 12 Aug 2016 20:38:05 GMT

Awesome, thanks for the fast response Sunny!

Thanks,

Ben V

Re: Creating a Percent Range for Selection

sunny_talwar — Fri, 12 Aug 2016 20:44:23 GMT

No problem

Re: Creating a Percent Range for Selection

benvatvandata — Fri, 12 Aug 2016 21:08:13 GMT

Quick question about your example:

When a narrow selection is made, the totals display ' - ' ...

I know I can change the 'Total Mode' to 'Sum of Rows', which works fine for the Customer Item Qty, but for Total Item Qty it sums every row (summing duplicates) giving a wrong total. (my actual report has many more itemcodes and customers than our example)

How can I get the sum of the Total Item Qty for distinct ItemCodes when such selections are made?

Thanks,

Ben V

Re: Creating a Percent Range for Selection

sunny_talwar — Fri, 12 Aug 2016 22:40:49 GMT

May be try now

Re: Creating a Percent Range for Selection

benvatvandata — Fri, 12 Aug 2016 23:22:56 GMT

I'm thinking this may not be possible the way I want to do it...

In your example, the second column is the "Total Item Qty" - although it displays values now, I want the total to be 16708000 because that is the Total quantity sold of that item. Is this possible?

Thanks,

Ben V

Re: Creating a Percent Range for Selection

sunny_talwar — Sat, 13 Aug 2016 18:45:38 GMT

Is this what we want?

Re: Creating a Percent Range for Selection

benvatvandata — Sat, 13 Aug 2016 20:08:13 GMT

Yes!! How did you do that?

Thanks,

Ben V

Re: Creating a Percent Range for Selection

sunny_talwar — Sat, 13 Aug 2016 21:02:00 GMT

I used the Dimensionality() function to use a different expression for the total and a different total for the regular rows.

=If(Dimensionality() = 0,

Sum(Aggr(If(If(Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty) >= vVarLow and

Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty) <= vVarHigh,

Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty)) > 0, Sum(Total <ItemCode> OrderedQty)), ItemCode)),

Sum(Aggr(If(If(Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty) >= vVarLow and

Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty) <= vVarHigh,

Sum(OrderedQty)/Sum(Total <ItemCode> OrderedQty)) > 0, Sum(Total <ItemCode> OrderedQty)), Customer, ItemCode)))

Re: Creating a Percent Range for Selection

benvatvandata — Sat, 13 Aug 2016 21:33:35 GMT

Oh sorry I didn't see your attachment in your message!

Wow those expressions are a handful... hah, this is getting closer, however when I filter 25%-75% the total displays 0 for the Total Item Qty.

Any other ideas?

Thanks,

Ben V