topic Re: Aggregation help? in QlikView

Aggregation help?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

I need the count distinct of USER_NAME which meet an IF condition per SUPERVISOR per week.

The fields used are USER_NAME, SUPERVISOR, DATE (MM/DD/YYYY).

The condition is

IF(

IF(Count(distinct

IF(OFF_ON_LINE='OFF-LINE',CASE#))=0,'-',Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#)))

(sum(IF(OFF_ON_LINE='OFF-LINE', aggr(IF(OFF_ON_LINE='OFF-LINE',HOURS),SUPERVISOR,OFF_ON_LINE,DATE,USER_NAME)))*70) >= 1,

USER_NAME)

The expression I use is

Count(DISTINCT Aggr(If(

IF(Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#))=0,'-',Count(distinct IF(OFF_ON_LINE='OFF-LINE',CASE#)))/(sum(IF(OFF_ON_LINE='OFF-LINE', aggr(IF(OFF_ON_LINE='OFF-LINE',HOURS),SUPERVISOR,OFF_ON_LINE,DATE,USER_NAME)))*70) >= 1,

USER_NAME), SUPERVISOR, DATE))

What am I doing wrong?

Re: Aggregation help?

vishsaggi — Tue, 31 Jul 2018 19:58:00 GMT

Can you share a sample to look into? And also what is the error you are getting?

Re: Aggregation help?

rwunderlich — Tue, 31 Jul 2018 19:59:59 GMT

Could you explain in words what the logic is?

-Rob

Re: Aggregation help?

haneeshmarella — Tue, 31 Jul 2018 20:04:40 GMT

The logic is

If the number of cartons picked divided by target cartons (Hours*70) >=1, then that User meets the target. I am trying to calculate the users count distinct based on that condition, per week per supervisor.

Does this help, Rob?