topic Re: Top 3 Plan Names by Rank (Stright Table) in QlikView

Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 03:01:41 GMT

Hi Folks, I want to show Top 3 Plans on each column like Top Plan 1, Top Plan 2 & Top Plan 3 in a straight Table.

I am using below expressions to calculate the Top 3 Plans in the straight table:

Top Plan 1: CONCAT(IF(Aggr(Rank(Sum(TQTY)),PlanName)=1,PlanName))

Top Plan 2: CONCAT(IF(Aggr(Rank(Sum(TQTY)),PlanName)=2,PlanName))

Top Plan 3: CONCAT(IF(Aggr(Rank(Sum(TQTY)),PlanName)=3,PlanName))

But the above expressions are not working properly? Is it possible can we do on the calculated dimension ?

Please find the attached files for reference.

Re: Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 04:22:44 GMT

Any one help me on this .....

Re: Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 13:16:25 GMT

Help Needed!...

Re: Re: Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 13:44:55 GMT

hi,

Check this attach file and let me know.

Regards,

Ashutosh

Re: Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 14:00:44 GMT

I want show Top Plan1, Top Plan2 , Top Paln3 as Columns in the table like below. The values should be changes based on the other selection like product id's:

TerrNo	Professional ID	Top Plan1	Top Plan2	Top Plan3
1	39079	HNQYQC CGUROH DCQNH	VBWYMZVW	PCH ARDUU ZGUSV SFUML
4	39079	HNQYQC CGUROH DCQNH	VBWYMZVW	PCH ARDUU ZGUSV SFUML

Re: Re: Top 3 Plan Names by Rank (Stright Table)

jerem1234 — Tue, 19 Nov 2013 15:02:59 GMT

I am not sure what it means for Plan Names to be the same, yet have different PlanId's. So I did the aggr's by PlanId instead of PlanName. But the table matches what u have for ID 39079.

PFA

Hope this helps!

Re: Top 3 Plan Names by Rank (Stright Table)

Tue, 19 Nov 2013 15:35:57 GMT

Why the Rank() function is not working ?

Can we get this on calculated dimension ?

Re: Re: Top 3 Plan Names by Rank (Stright Table)

jerem1234 — Tue, 19 Nov 2013 16:35:02 GMT

Attached is the way to do it with rank, you needed to aggregate on TerrNo and Professional_Id as well (I also did PlanId instead of PlanName). Also, I switched the mode of rank for the second parameter to 4 to handle the ties (instead of it showing null).

As for calculated dimensions, you cannot use the function concat since it is an aggregation function. Here is a snippet from Qlikview Help:

Add calculated dimension...

Adds a new dimension and opens it for editing in the Edit Expression Dialog dialog. A chart dimension is often in a single field, but can also be dynamically calculated. A calculated dimension consists of an expression involving one or more fields. All standard functions may be used. Aggregation functions may not be used, but the Advanced Aggregation function can be included for achieving nested aggregation.

So if you wanted to do it that route, you would have to do it through another way, maybe script?

Hope this helps!

Re: Top 3 Plan Names by Rank (Stright Table)

jerem1234 — Tue, 19 Nov 2013 17:19:38 GMT

Do you need Top Plan 1, Top Plan 2, and Top Plan 3, all in one table? If you are in need of using calculated dimensions, you can separate out and use different tables for the 3.

Just strip concat and use the formula for 1 as calculated dimension:

=IF(Aggr(Rank(Sum(TQTY),4),TerrNo, Professional_Id, PlanId)=1,PlanName)

Just can't have both Top Plan 1, Top Plan 2, and Top Plan 3 all in one table as calculated dimensions.

Hope this helps!