topic Re: Expression calculation in bar chart with group by in App Development

Expression calculation in bar chart with group by

narender123 — Wed, 07 Jul 2021 14:04:25 GMT

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

Re: Expression calculation in bar chart with group by

Kushal_Chawda — Wed, 07 Jul 2021 14:41:14 GMT

@narender123 try below

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

Re: Expression calculation in bar chart with group by

narender123 — Thu, 08 Jul 2021 07:26:34 GMT

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,

Re: Expression calculation in bar chart with group by

Stephenson6 — Fri, 09 Jul 2021 04:22:27 GMT

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

ACES ETM

Re: Expression calculation in bar chart with group by

MayilVahanan — Thu, 08 Jul 2021 08:21:07 GMT

Might be, try like below

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

Re: Expression calculation in bar chart with group by

narender123 — Thu, 08 Jul 2021 08:28:30 GMT

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.

Re: Expression calculation in bar chart with group by

narender123 — Thu, 08 Jul 2021 08:58:40 GMT

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.