topic Re: Selection on pivot table in QlikView

Selection on pivot table

Thu, 03 Sep 2015 09:54:54 GMT

Hi, I have a question about selection in pivot table.

here is my data:

What i expect is, when i insert percentage, let's say 60 to 68. I want to check the quantity for each brand of one person. If at least one brand in the range of 60-68 i want to show all the data. We can see that brand A from person A has percentage 67%. because the requirement is at least one data, the condition return true and show all data of person A. here is the result I expect:

Person B not shown because none of each brand has fullfill the range between 60 to 68.

Really appreciate your help.

Thanks

Re: Selection on pivot table

Thu, 03 Sep 2015 09:56:07 GMT

I also attach my sample qvw file here.

thanks

Re: Selection on pivot table

tresB — Thu, 03 Sep 2015 11:15:31 GMT

May be like attached sample. Check the variables I created to grab the concept.

Re: Selection on pivot table

Fri, 04 Sep 2015 01:59:35 GMT

Hi Tres,

thanks for your answer, you are a great problem solver.

but i want to ask, how if i have more than two dimension, not only person or brand. let's say i have area dimension and staff name. which part i must change ?

thanks

Re: Selection on pivot table

tresB — Fri, 04 Sep 2015 05:29:50 GMT

If it is only about increasing dimension, you have to look into the variable -

vPersonEligibility

=Concat(Aggr(If(

$(vPercentFormula)*100>=60 and $(vPercentFormula)*100<=68,

Person

),Person, Brand, Area, StaffName)

)

Include the new dimensions under Aggr() scope, like shown above.

Re: Selection on pivot table

Fri, 04 Sep 2015 05:44:52 GMT

Hi Tres,

Do i need to add dimension combination in if() scope ? like this:

=Concat(Aggr(If(

$(vPercentFormula)*100>=60 and $(vPercentFormula)*100<=68,

Person&Area&StaffName

),Person, Brand, Area, StaffName)

)

because it will be some combination of the dimension

thanks

Re: Selection on pivot table

tresB — Fri, 04 Sep 2015 05:53:08 GMT

Try wihout that. If that doesn't give you what you need, try to share the sample with those additional dimension and expain the expected output.

Re: Selection on pivot table

Fri, 04 Sep 2015 07:55:44 GMT

Hi Tres,

I've apply your solution in large data. But unfortunately it runs very slow when loading (calculating) the data. Do you have any idea to improve the performance ?

thanks

Re: Selection on pivot table

tresB — Fri, 04 Sep 2015 08:18:10 GMT

Yes, use of aggr() might be causing the performance hit. To improve it you have to trade off; take it to the script if possible (because that would increase the reload time).

Re: Selection on pivot table

Fri, 04 Sep 2015 08:22:41 GMT

Hi Tres,

thanks for your response. Still don't get the idea. Can you explain about take it to the script ?

can you give me some example please ?

Really appreciate your help.

Thanks

Re: Selection on pivot table

tresB — Fri, 04 Sep 2015 08:33:32 GMT

You can aggregate and use the similar logic to identify and flag the Person members in the script, like:

Load

....

If(Sum(...)/Sum(...).., 'Yes', 'No') as PersonFlag

From <> group By.......;

Now, this flag can be easily used in the front end to filter your Persons.. This will reduce the overhead in the front-end, however, it becomes a static kind of analysis and takes toll on reload time.

Re: Selection on pivot table

Fri, 04 Sep 2015 08:38:29 GMT

Hi Tres,

Would you mind to modify my sample file to get the result I expect Tres ?

Really confuse about it.

Big thanks to you for helping me.

Here i attach my raw data (excel)

Thanks