Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
On further investigation, the problem seems to be with the field Name_Month's format:
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
I've fixed this bug, however, the calculation still isn't working
Can you share your new qvw document?
please share the new QVW file
!
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?
I guess so. Because now it shows zero