topic Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider in QlikView

Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 06:15:52 GMT

Hi All,

I have an issue with a set analysis table and grouping or aggregation on 2 dimensions with a Slider for selecting Top customers Sales.

Have tried several combinations with no luck,

please see attached example I put together.

thanks

reagrds Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

perumal_41 — Wed, 20 Feb 2013 06:44:21 GMT

Please Explain little bit more for my understanding

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 07:26:20 GMT

I wish to see in the table list of Top Customers by sales but with ALL Areas listed or Grouped next to Customers.

Normally if I click the top of Sales for 2013 column to sort the sales highest to lowest, it will show and sort by Customer Names and only 1 Area the sale was made highest to lowest.

I wish Show all Areas as a default set Block attached to top Customer Names.

So if harry has made top sales in even only area say 'Parts' then the rest of areas are shown as a default block listed next to harrys Name even if Building has $1.00 in it but harry's total sales are still top in example.

Customer Name     Area                    Sales 2013

harry                                         Building     1.00
harry                                         Parts          234.00
harry                                         Safety        2324.00

peter                                         Building     134.00
peter                                         Parts          234.00
peter                                         Safety         2.00

sorry I hope that explains it better ...not thinking straight

Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

perumal_41 — Wed, 20 Feb 2013 08:28:06 GMT

I have uploaded one application please check is correct or not.

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 09:04:42 GMT

thanks for your reply and example.

I think you are almost there

see my attached sorted exampIe, it needs harry's sales for " Building" appended to harry's other Areas "Safety and Parts"

so Areas show all togehter for harrys sales.

then you have it

Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Gysbert_Wassenaar — Wed, 20 Feb 2013 09:22:27 GMT

If you're using QV11 you can use dimension limits. See attached qvw. Or you could use this expression Sum({<Customer_Name={"=rank(sum(total <Customer_Name> Sales))<=$(vTopCust)"}>}Sales).

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 09:51:09 GMT

Hi again Gysbert,

how have you been keeping ?

thanks for reply I have looked at your example and used the expression with variable Top Customer it works

to a point then falls over ?? if you place your expression in and use slider for 1 2 3 top customers the data shows

correct in sort order highest to lowest. but now move slider to 4 and john is above bills sales when he clearly is not ?

john should be bottom.

regards Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

perumal_41 — Wed, 20 Feb 2013 09:58:09 GMT

I have uploaded one application please check is correct or not.

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 10:21:46 GMT

I have had a look and no, see file attached this is correct see how it behaves select 1 top customer is shows 3 Areas for top customer

this still has a problem, all good up to this slider 3 selection all sales are in sort order for top customers

but then when we select 4 is shows John is above bills sales

john should be on bottom ....can not see what is causing this ?

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Gysbert_Wassenaar — Wed, 20 Feb 2013 10:27:13 GMT

Let's try a calculated dimension then. See attached qvw.

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 10:32:40 GMT

Yes Gysbert that is correct and thank you

I notice you used the

=aggr(dual(Customer_Name,sum(Sales)),Customer_Name)

in dimension what does the Dual actually do ?

Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

perumal_41 — Wed, 20 Feb 2013 10:33:35 GMT

I have uploaded one application please check is correct or not.

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Peter_Brunner — Wed, 20 Feb 2013 10:34:24 GMT

Thanks for your efforts, Gysbert has solved it

thank you agian

and sorry if I did not explain it properly, thats my fault!

regards Peter

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Gysbert_Wassenaar — Wed, 20 Feb 2013 11:10:29 GMT

The dual function creates a 'value' with both a textual and numeric representation. In this case the numeric value is the sum of the Sales for the customer and the textual value is the name of the customer. That way you can sort it numerically and still display it as customer name.

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Thu, 18 Apr 2013 08:06:56 GMT

Excellent example. Thank you very much! 🙂

Re: Help Set Analysis, Grouping of Top Customer Sales and Area Group using Top 10 Slider

Wed, 29 Apr 2015 14:43:48 GMT

Big thank you Gysbert! Your response to this question pulled me out of a pit of despair and made my report work the way the client wants.