Aggr, sum with if statement in pivot

[#1]

Hello, guys!

I have a pivot, where I need to calculate sum of values according to the condition: if Fact_Bench( the field in the other table) = 0

My expression  in pivot: Sum(Aggr(IF(Fact_bench=0,Sum(Bench_Data*Forecast*Sal*1.10),Sum({<Y={'2015'}>}Fact_bench)), Month_num, BG_bench)).

It's not working, it seems to be working only if this condition is false.

Sample app is attached.

Please help!

[sunny_talwar]

Seems like a problem with you data model. Forecast and Bench_Data which are multiplied together are not in one row (Please see the attached image)

That's why it must be giving you 0 when Fact_Bench = 0

HTH

Best,

Sunny

[sunny_talwar]

On further investigation, the problem seems to be with the field Name_Month's format:

[sunny_talwar]

Forecast:

Load

Name&'|'& Date(Date(Num#(Month)),'MMM-YY')as Name_Mon,

Data as Forecast

Resident Temp;

Drop Table Temp;

Bench_Forecast:

LOAD

AutoNumberHash128(BG_bench,Month_Date) as Key,

Emp_name,BG_bench, Month_Date,Bench_Data,Position,

num(Month_Date) as Month_num,

Emp_name &'|'& Month_Date as Name_Mon

Resident Bench

where not match(Position, 'Junior Support Specialist','Support Specialist','Senior Support Specialist', 'Junior IT Coordinator', 'IT Coordinator',

'Senior IT Coordinator', 'Junior IT Operator', 'IT Operator', 'Senior IT Operator','Outstaffing Specialist','Consultant','Senior Consultant');

Drop Table Bench;

In Bench_Forecast you are using Month_Date which has only MonthName (Jan, Feb, Mar...) where as Forecast Table uses Date format 'MMM-YY'). You need to align these two.

I hope this helps.

Best,

Sunny

[#5]

I've fixed this bug, however, the calculation still isn't working

[sunny_talwar]

Can you share your new qvw document?

[Anonymous]

please share the new QVW file

[#8]

!

[sunny_talwar]

Trying to troubleshoot what is your expected output for Month_Date: Sep and Business Group: Itvjvkoiobe muiyi qzmnll

Does it need to be 5,819?

[#10]

I guess so. Because now it shows zero