AGGR function issue

[Anonymous]

Hello All,

I have below data sets

Territory	User	Sales
E0	SNDP	10.56
E0	SNDP
E1	NPD	12
E1	NPD	11
E1	NPD	13

I need to show Sales Territory wise

Chart Format

Territory	Sales
E0	10.56
E1	36

But internally, it should show data according to user,

If I use Aggr, it shows Data for small data set like above correctly, but if i take large dataset and multiple other relevant columns, then chart shows incorrect data for those territoies where null Sales are present. Overall Sales display null which should not happen.

Regards,

Nita Prasad

[MK_QSL]

Create a straight table

Dimension

Territory

Expression

SUM(Sales)

[Anonymous]

Thanks Manish.

Yes I have created the straight Table. In the expression, I need to sum data according to user... like we do in pivot table.

(aggr(If(Sum(Sales)=0,null(), sum(Sales)),User))

It works fine for this small data set, but for complex data set where multiple users have null date, plus some data for different time, in that case my expression return null.

Regards,

Nita Prasad

[MK_QSL]

Sorry but not understood your question properly.... Provide some more information along with sample or dummy data and expecting result...

[giakoum]

what is the use of the if statement? this should be enough :

Rangesum(aggr(sum(Sales),User))

[Anonymous]

Hello All,

Hopefully, you can understand more clearly what issue I am facing? I have attached sample file with this reply.

Thanks and Regards,

Nita Prasad@

[giakoum]

Sum of rows in the expression tab

[MK_QSL]

What you want to get as an output... i.e. what is the logic behind using below expression

rangesum(aggr( sum(Sales),User))

[Anonymous · Accepted solution]

Hello All,

I finally understood, where I was doing mistake.

I need to show territory wise average, min , max sale .. for all the sales person.

I should have kept Territory explicitely in the expression, that should have solved my problem.

aggr( sum(Sales),User,Territory))

It solved my problem.

Thanks both of you, for taking time out to understand my requirement.

Thanks and Regards,

Nita Prasad