Hi,
I need help with mixing aggregate and non aggregate arguments.
So ai m trying to use different logic base don [Type]
IF [Type] = "Villager" THEN SUM([Planned Hours])/(SUM([Total Hours Available])-SUM(IF [Include Training]="Yes" THEN [Training] ELSE 0 END)-SUM(IF [Include Loaned]="Yes" THEN [Loaned] ELSE 0 END) -SUM(IF [Include General Admin]="Yes" THEN [General Admin] ELSE 0 END) )
ELSEIF [Type] = "King" THEN (SUM([Planned Hours]) + SUM([Other Audit Hours])) /(SUM([Total Hours Available])-SUM(IF [Include Training]="Yes" THEN [Training] ELSE 0 END)-SUM(IF [Include Loaned]="Yes" THEN [Loaned] ELSE 0 END) -SUM(IF [Include General Admin]="Yes" THEN [General Admin] ELSE 0 END) )
ELSEIF [Type] = "Queen" THEN (SUM([Planned Hours]) + SUM([Other Audit Hours])) /(SUM([Total Hours Available])-SUM(IF [Include Training]="Yes" THEN [Training] ELSE 0 END)-SUM(IF [Include Loaned]="Yes" THEN [Loaned] ELSE 0 END) -SUM(IF [Include General Admin]="Yes" THEN [General Admin] ELSE 0 END) )
END
What am I doing wrong here?
@Jonathan Rajbahadursingh
Hi, I think the calculated field will explain your problem:
(ZN(SUM(IF [Type] IN ("Queen","King","Villager") THEN [Planned Hours] ELSE 0 END)) + ZN(SUM(IF [Type] IN ("Queen","King") THEN [Other Audit Hours] ELSE 0 END)))
/
(ZN(SUM([Total Hours Available]))-ZN(SUM(IF [Include Training]="Yes" THEN [Training] ELSE 0 END))-ZN(SUM(IF [Include Loaned]="Yes" THEN [Loaned] ELSE 0 END)) -ZN(SUM(IF [Include General Admin]="Yes" THEN [General Admin] ELSE 0 END)))
Basically, you need to calculate the conditional inside the sum, and then aggregate the result. I have also added ZN to guarantee you get results in case of null values in the sum aggregations.
If this post resolves the question, would you be so kind to "Select as Best"?. This will help other users find the same answer/resolution and help community keep track of answered questions. Thank you.
Regards,
Diego Martinez
Tableau Visionary and Forums Ambassador
