Expression calculation in bar chart with group by

[narender123]

Hello Team,

I have a straight table where I put a expression like 
No. of Decleration = Count( distinct reg_num) 

Note: I have  selected the radio button sum of rows in the expression tab of chart property.

it is showing result =2090

Example:

I have to show the same result in bar chart. I am using the same expression like          = Count( distinct reg_num) 

But getting wrong data in the chart as it is not applying group by on dimensions office_code and section.

How to apply group by on chart level and get the same total amount as in straight table?

I used aggr function but did not get result.

aggr(distinct reg_num,section,off_cod)

Thanks,

Narender

[Kushal_Chawda · Accepted solution]

@narender123  try below

=sum(aggr(Count( distinct reg_num) , section,off_cod))

[narender123]

Hi Kush,
Thanks for your reply.

Point 1: This works fine for the 1 year like

 =sum(aggr(Count({<Year={$(=max(Year))}>} distinct reg_num) , section,off_cod))

Point 2: But I need to show last five count. so it is not showing the last 5 years, also showing the wrong data for 2021.

 =sum(aggr(Count({<Year={'>=$(=max(Year)-4)'}>} distinct reg_num) , section,off_cod))

Point 3: My last 5 year count works when I don't use aggr but getting wrong data count

=Count({<Year={'>=$(=max(Year)-4)'}>} distinct reg_num)

Please tell me, how to show last 5 year count with aggr.

Thanks,

[Stephenson6]

I really appreciated your post for the new user I really like this .

ACES ETM

[MayilVahanan]

HI 

Might be, try like below

=sum(aggr(Count( {<Year={'>=$(=max(Year)-4)'}>} distinct reg_num) , Year, section,off_cod))

[narender123]

Hi  MayilVahanan ,

Thanks for reply.

I tried it already,

But it shows only 2021 year count which is right count for 2021 but it need to show 2017,2018,20199,2020 count also.

Please advice.

[narender123 · Accepted solution]

It is working fine now.

=sum({<Year={'>=$(=max(Year) -4) '}>} aggr(count ({<Year={'>=$(=max(Year) -4) '}>}distinct reg_num) , section, off_cod, year))

Thanks, Guys for your reply.