Aggr() with If and by Dimension

[suneethi]

Hi,

I am trying to get a count of ABCD scores by month

A B C D values are assigned based on the number of on time arrival rate in a given fiscal month for suppliers. 

Pick(Match(ValueList('A', 'B', 'C', 'D'), 'A', 'B', 'C', 'D')
,Count(Distinct Aggr(If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.95, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.90 and Sum([Total On-Time])/Count([Performance Code]) < 0.95, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.85 and Sum([Total On-Time])/Count([Performance Code]) < 0.90, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(If(Sum([Total On-Time])/Count([Supplier Name]) < 0.85, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
)

Expected results - 

ABCD	PO Receipt Fiscal Month	Above Formula
A	Sept-20	37
B	Sept-20	6
C	Sept-20	9
D	Sept-20	43
A	Oct-20	49
B	Oct-20	3
C	Oct-20	5
D	Oct-20	33

Returned results - 

ABCD	PO Receipt Fiscal Month	Above Formula
A	Sept-20	37
B	Sept-20	0
C	Sept-20	0
D	Sept-20	0
A	Oct-20	49
B	Oct-20	0
C	Oct-20	0
D	Oct-20	0

Can someone help me to see why only first option is getting populated here?

Thanks!!

[Oleg_Troyansky · Accepted solution]

Hi there!

This is one of the common confusing issues with the AGGR function - it doesn't work well with Synthetic dimensions (created by ValueList). We were just describing it in detail in my class on Set Analysis and AGGR 2 days ago at the first virtual session of the Masters Summit for Qlik...

It's impossible to explain everything in one message, but in a nutshell, I'd recommend replacing the ValueList in the Dimension and a complex set of AGGR functions in the measure with a much simpler structure:

- In the dimension, use AGGR() to calculate A, B, C, D, instead of creating them as a list:

AGGR( IF(....., A,

              IF(..., B,

              IF(...., C,

             IF (...,D ))))

,         Supplier, Month)

Then, in the Measure, simply count Suppliers. That simple.

If you'd like to take part in the most advanced Qlik training, check out our agenda for the future sessions - Performance Tuning in December, Advanced Scripting in January, Data Modelling in February and more.

Cheers,

[suneethi]

Thanks a lot for the reply Oleg!! I was not able to figure out the Dimension Aggr() with the if statements, but you put me in the right direction to search and this support page seemed to help me with the issue. So when I added NODISTINCT inside the above Aggr(), it seems to be working.

https://support.qlik.com/articles/000031589

What I ended up - 

Pick(Match(ValueList('A', 'B', 'C', 'D'), 'A', 'B', 'C', 'D')
,Count(Distinct Aggr(NODISTINCT If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.95, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(NODISTINCT If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.90 and Sum([Total On-Time])/Count([Performance Code]) < 0.95, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(NODISTINCT If(Sum([Total On-Time])/Count([Supplier Name]) >= 0.85 and Sum([Total On-Time])/Count([Performance Code]) < 0.90, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
,Count(Distinct Aggr(NODISTINCT If(Sum([Total On-Time])/Count([Supplier Name]) < 0.85, [Supplier Name]),[Supplier Name],[PO Receipt Fiscal Month]))
)

Your classes looks like an awesome training. Will check it out definitely. 

Thanks a lot!!

- Off to calculate a % on this 🙂

[suneethi]

Hi Oleg, 

I got the formula for your dim idea working as well. Just as you said my dim looks like this now - Instead of valuelist('A','B','C','D') - 

=AGGR(IF((Sum([Total On-Time])/Count([Performance Code]))>=0.95,'A',
IF((Sum([Total On-Time])/Count([Performance Code]))>=0.90 and Sum([Total On-Time])/Count([Performance Code]) < 0.95,'B',
IF((Sum([Total On-Time])/Count([Performance Code]))>=0.85 and Sum([Total On-Time])/Count([Performance Code]) < 0.90,'C',
IF((Sum([Total On-Time])/Count([Performance Code]))<0.85,'D')))),[Supplier Name], [PO Receipt Fiscal Month])

and replaced the measure with just count(distinct [Supplier Name]).

Thanks a lot for the reply, really appreciate it!!