Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
Creator II

Aggregate Calculation Dimension Help

I have the following columns

TOTAL CARTONS Count(distinct CASE#)

TOTAL PIECES num(sum(UNITS#),'#,##0')

TARGET CARTONS sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29

TARGET PIECES sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*74

I am trying to COUNT DISTINCT the USER_NAME for each row in the column OPERATORS AT RATE based on condition that TOTAL CARTONS >= TARGET CARTONS something like this:

aggr(count(distinct IF(TOTAL CARTONS >= TARGET CARTONS, USER_NAME), DIMENSION....)


What dimension do I use here to get the right output of OPERATORS AT RATE for each SUPERVISOR per WEEK?


SS.PNG

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT Aggr(

If(

Count(DISTINCT CASE#)/(Sum(Aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29) >= 1 or

Sum(UNITS#)/(Sum(Aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*74) >= 1,

USER_NAME), SUPERVISOR, USER_NAME))

View solution in original post

19 Replies
CarlosAMonroy
Creator III
Creator III

Hi Haneesh,

You can try the following:

if( TOTAL CARTONS >= TARGET CARTONS, sum(aggr(count(distinct USER_NAME),WEEK_START)) )

Hope that works,

Carlos M

haneeshmarella
Creator II
Creator II
Author

Hi Carlos,

I don't get any output. I just get '-'.

neelamsaroha157
Specialist II
Specialist II

May be you can use i- if(column(2)>column(4), UserName), you might not aggregation function.

haneeshmarella
Creator II
Creator II
Author

Already tried this Neelam, it did not work.

neelamsaroha157
Specialist II
Specialist II

Can you share a sample??

CarlosAMonroy
Creator III
Creator III

Would be useful if you share your example. Also check the field names match your data model.

Thanks,

Carlos

vishsaggi
Champion III
Champion III

Hope you are using square brackets around your label names like [Total Cartons] >= [Total Targets]

did you try like below:

= IF(Count(distinct CASE#) >= sum(aggr(HOURS,USER_NAME,DATE,SUPERVISOR))*29 , USER_NAME)

haneeshmarella
Creator II
Creator II
Author

Yes, I did use [ ] and also did the above earlier but it did not work.

haneeshmarella
Creator II
Creator II
Author

What do you want me to exactly share? It's an official document hence cannot share the qvw