topic Re: Rank in the bar chart in QlikView

Rank in the bar chart

kanalavs — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I am new to the community. I have the requirement to display bar chart based on highest priority.

If a person has priority 1 and 2 then we need to consider the priority based on the latest date.

Please help how to write the rank script for the bar chart. You can find the data below.

Fruit	Person No	Rank	Date
Apple	SXXXX1	1	20180718
Apple	SXXXX2	1	20180925
Apple	SXXXX3	1	20181119
Apple	SXXXX4	1	20180625
Apple	SXXXX4	1	20181016
Apple	SXXXX5	1	20180108
Apple	SXXXX6	1	20180815
Apple	SXXXX7	1	20180813
Apple	SXXXX7	1	20180814
Orange	SXXXX1	2	20180718
Orange	SXXXX6	2	20180815
Banana	SXXXX2	3	20180925
Banana	SXXXX6	3	20180815
Grape	SXXXX1	5	20180718
Banana	SXXXX3	3	20181026
Grape	SXXXX5	5	20171124
Orange	SXXXX5	2	20180101

Attached Sample of the chart:

Re: Rank in the bar chart

shiveshsingh — Thu, 03 Jan 2019 06:48:14 GMT

What do you mean by priority here? How are you defining it?

Re: Rank in the bar chart

kanalavs — Thu, 03 Jan 2019 07:04:10 GMT

Priority is defined in backend load by considering Apple priority-1, Orange-2, Banana-3, Grape-5.

we have to display the chart by displaying the number of persons tagged for the fruit name based on the rank.

If a person is tagged both apple and Orange then the chart has to consider the person id only for apple as apple is priority-1.

Please let me know if you need any other info.

Re: Rank in the bar chart

uacg0009 — Thu, 03 Jan 2019 10:00:15 GMT

Hi Kanalavs,

To be honest, I didn't get the real result that you want.

So could you please tell me what's the result you want according to the data you gave above?

Thanks

Aiolos Zhao

Re: Rank in the bar chart

shiveshsingh — Thu, 03 Jan 2019 11:54:59 GMT

Couldn't get the logic 😞

Re: Rank in the bar chart

kanalavs — Sat, 05 Jan 2019 03:23:59 GMT

Hi Aiolos Zhao,

I have fields as follows:

Action- outcome action

C-ID-Person number

Rank-rank (pre-defined as (CA : 1, DA :2, WA : 3, NA : 5)

Date: Created Date

Requirement: Need to count the number of persons for each Action item based on the rank.

rank1 is the highest. If a person tagged to rank 1 and rank2 then we need to count the person tagged to rank1 and ignore the count for rank2.

Please see the below outcome results based on the aggr function that I defined:

highest priority CA is mapped to 18 persons but few of the persons are also tagged to DA, WA, NA, please see pic2. Based on the requirements, i need to ignore lower rank DA,WA,NA for those repeated persons and consider only CA.

pic1:

[Action]	aggr(count([C_ID]),RANK,[Action])
CA	18
DA	2
WA	2
NA	1

Pic2:

Action	[C_ID]
CA	PNXXX1
CA	PNXXX2
CA	PNXXX3
CA	PNXXX4
CA	PNXXX5
CA	PNXXX6
CA	PNXXX7
CA	PNXXX8
CA	PNXXX9
CA	PNXXX10
CA	PNXXX11
CA	PNXXX12
CA	PNXXX13
CA	PNXXX14
CA	PNXXX15
CA	PNXXX16
CA	PNXXX17
CA	PNXXX18
DA	PNXXX7
DA	PNXXX15
WA	PNXXX11
WA	PNXXX15
NA	PNXXX7

Re: Rank in the bar chart

uacg0009 — Mon, 07 Jan 2019 02:09:40 GMT

Hi,

So the result you expect is below table?

[Action]	Num
CA	18
DA	0
WA	0
NA	0

am I right? if not, please show your expect result.

Aiolos

Re: Rank in the bar chart

kanalavs — Tue, 08 Jan 2019 03:27:04 GMT

Hi Aiolos,

Yes, the output should be

[Action]	Num
CA	18
DA	0
WA	0
NA	0

I tried aggr and firstsortedvalue functions but could not able to get the above result.

Re: Rank in the bar chart

uacg0009 — Tue, 08 Jan 2019 08:02:37 GMT

Hi Kanalavs,

I don't know whether you can left join a table in back-end,

I left join a rank table in back-end, and change the data source for testing the expression,

I think below is what you want :

Aiolos Zhao

Re: Rank in the bar chart

kanalavs — Tue, 08 Jan 2019 08:24:45 GMT

Aiolos Zhao,

Backend, I did leave join and the outcome results should be as specified below. DA, WA, and NA should be 0 because the persons under these elements also belong to CA and CA is the high priority.

[Action]	Num
CA	18
DA	0
WA	0
NA

Re: Rank in the bar chart

kanalavs — Tue, 08 Jan 2019 08:50:38 GMT

Hi Aiolos Zhao,

i have written the expression as follows but the outcome is similar like you. some of the persons are tagging to both CA and DA. Please advise me how to restrict the persons.

Dimension:

AGGR(FirstSortedValue([Action],RANK_Level),[C_ID])

Expression:

Count(distinct(C_ID))

Re: Rank in the bar chart

uacg0009 — Wed, 09 Jan 2019 01:43:59 GMT

Have you seen my qvw?
I said I add the data source for testing the expression, have you seen it?
I think my expression is what you want, if not, please tell me what you want using my data.