topic Re: AGGR function issue in QlikView

AGGR function issue

Anonymous — Fri, 15 Aug 2014 11:38:03 GMT

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

Re: AGGR function issue

MK_QSL — Fri, 15 Aug 2014 11:50:42 GMT

Create a straight table

Dimension

Territory

Expression

SUM(Sales)

Re: AGGR function issue

Anonymous — Fri, 15 Aug 2014 11:56:03 GMT

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

Re: AGGR function issue

MK_QSL — Fri, 15 Aug 2014 11:58:19 GMT

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

Re: AGGR function issue

giakoum — Fri, 15 Aug 2014 12:00:31 GMT

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

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

Re: AGGR function issue

Anonymous — Fri, 15 Aug 2014 12:42:33 GMT

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@

Re: Re: AGGR function issue

giakoum — Fri, 15 Aug 2014 12:46:54 GMT

Sum of rows in the expression tab

Re: AGGR function issue

MK_QSL — Fri, 15 Aug 2014 12:48:56 GMT

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

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

Re: AGGR function issue

Anonymous — Fri, 15 Aug 2014 13:26:05 GMT

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