topic Re: Compare values of Same Dimension within Pivot Table after selection in QlikView

Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Tue, 17 Apr 2018 13:56:06 GMT

Hello,

I have a Pivot table as shown in the image:

As you can see, the data shown in the table is for a specific selected supplier, 'Supplier G'. The functionality that I need to have is to be able to compare the measures within the table with those ones of a 'identical group' or a 'competitor group' rather of suppliers which can be chosen from the list of suppliers available within the Supplier Field. For example, let's say Suppliers A,C,E are suppliers who are direct competitors of Supplier G. So, I want to be able to select the Suppliers A,C and E from the list of all available Suppliers and then show the measures('Spend Est.,000nd' and 'Quoted Cost by Supplier,000nd') of these 3 suppliers beside the 2 measures of Supplier G(which is currently selected).

I have tried using the dropdown option for the dimension within Pivot Table but this removes my current selected 'Supplier G' and messes up the rank functions order used within my dimensions L1,L2 and L3. I am unable to think of another way this can be done. Please help me out in achieving this. I'm attaching the app as well. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Tue, 17 Apr 2018 14:05:03 GMT

How do you know that Supplier A, C, and E are direct competitors of G? Is this defined somewhere in the dashboard?

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Tue, 17 Apr 2018 14:16:20 GMT

No, the user knows this. He/she will select the suppliers from his/her knowledge.

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Tue, 17 Apr 2018 14:20:17 GMT

I guess create an inland field SupplierUserSelection and then use that in the set analysis for the other two expressions... I can show you how to do this if you can run the attached app

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Tue, 17 Apr 2018 14:27:14 GMT

Hi Sunny. Here's the reloaded file !

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Tue, 17 Apr 2018 14:32:46 GMT

Apologies! Reuploaded the correct file now!

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Tue, 17 Apr 2018 14:38:40 GMT

Does this look like what you wanted?

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 01:55:51 GMT

Almost close. I need the measures for all 3 suppliers to be side by side in separate columns though. Not aggregated together. Is there anyway to do this? Thanks Sunny!

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Wed, 18 Apr 2018 02:06:49 GMT

Like this?

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 05:56:16 GMT

Yes! This is exactly what I wanted Sunny. If I understood this correctly, you used a set analysis expression with the P() function to do this after creating an island table with the Suppliers field. Is that all or am I missing something? Thanks a ton again Sunny! I'm learning a lot thanks to you! Hopefully I can reach a level when I can help out people like you someday!

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 07:30:19 GMT

Hi Sunny! I have one last question. I'm trying to add in another measure named 'Delta to Cherry Pick' which calculates the difference between best quotes for that particular L1. For example, in the below image for L1 '4Serv116', the best(minimum) quoted price is that of Supplier E and the difference between the quote of Supplier B and Supplier E comes out to be 575, which is the calculated Delta to Cherry Pick measure.

Now the issue I'm facing is this measure is being calculated only for the Supplier being selected from the original Supplier field. The measure is not being calculated for Suppliers being selected from the 'SupplierUserSelection' field as you can see(all are coming out as 0s). How do I modify my expression so that this measure is calculated for all the 3 Suppliers?

To make it even more clear, for example, for the same L1 '4Serv116', for Supplier A this measure should be 1200(Supplier A Quote 2950-Least Quote(Supplier E)1750=1200) but it is 0 right now. Similarly for Supplier C it should be 1300 and for Supplier E it should be 0. Here's the expression I'm using for this measure:

sum({<Supplier = p(SupplierUserSelection)+p(Supplier)>}

aggr({<Supplier = p(SupplierUserSelection)+p(Supplier)>}([Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)-min({<Supplier = p(SupplierUserSelection)+p(Supplier)>}TOTAL <[Baseline #]> [Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)))*Volume, [Baseline #],Supplier)

)/1000

Attaching the file again in case you need it. Thanks a ton again!

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Wed, 18 Apr 2018 08:55:01 GMT

Try this

sum({<Supplier = p(SupplierUserSelection)+p(Supplier)>}

aggr((Only({<Supplier = p(SupplierUserSelection)+p(Supplier)>}[Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate))-min({<Supplier = p(SupplierUserSelection)+p(Supplier)>}TOTAL <[Baseline #]> [Quote Price]*([FX_Rate]/[FX Rate])*(1-Rebate)))*

Only({<Supplier = p(SupplierUserSelection)+p(Supplier)>}Volume), [Baseline #],Supplier)

)

/1000

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Wed, 18 Apr 2018 08:56:23 GMT

Yes you are right in your understanding

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 09:04:45 GMT

I get it now. I overlooked including the set expression for the Volume part. Thanks a lot again Sunny!

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Wed, 18 Apr 2018 09:06:23 GMT

Also, you had set analysis for the Aggr... whereas I added Only function within Aggr to add set analysis

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 09:08:05 GMT

If you dont mind can you please explain how the Only function here makes the difference within the aggr part?

Re: Compare values of Same Dimension within Pivot Table after selection

sunny_talwar — Wed, 18 Apr 2018 09:09:46 GMT

Running for work right now... will try to get back to this at a later time.

Best,

Sunny

Re: Compare values of Same Dimension within Pivot Table after selection

mrthomasshelby — Wed, 18 Apr 2018 09:10:49 GMT

Thanks a ton again Sunny!