Rank in the bar chart

[kanalavs]

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:

[shiveshsingh]

Hi

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

[kanalavs]

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.

[uacg0009]

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

[shiveshsingh]

Hi

Couldn't get the logic 😞

[kanalavs]

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

[uacg0009]

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

[kanalavs]

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.

[uacg0009]

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

[kanalavs]

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