Calculated Dimension with Aggr and IF

brucemazynzolt — Sat, 16 Nov 2024 00:53:28 GMT

Hi Experts,

I have a requirement in Pivot table calculated dimension only like below

Calculated dimension -> Rate

=If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), Text(([Rate])))

Calculated dimension -> Time

=num(aggr(sum(Overtime),Region,Team,EmpType,PName,TName),'#,##0.00')

Here my requirement is to create another calculated dimension by multiply the Rate and Time calculated dimensions

I tried like below types but getting 0 value or N/A

=num(aggr(sum(Overtime)*(If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), Text(([Rate])))),Region,Team,EmpType,PName,TName),'#,##0.00')

=If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), (Text(([Rate]))*(num(aggr(sum(Overtime),Region,Team,EmpType,PName,TName),'#,##0.00')))

Below the sample output getting but if anything *0 should come 0

Rate	Time	Cost
92.400000	36.000	3,326.400
92.400000	5.000	462.000
0	9.000	782.910
0	9.000	782.910
0	9.000	120.420

Need to achieve like below table

Rate	Time	Cost
92.400000	36.000	3,326.400
0	9.000	0
0	9.000	0
0	9.000	0
92.400000	5.000	462.000
92.400000	0	0

Kindly help me to achieve

Re: Calculated Dimension with Aggr and IF

brucemazynzolt — Wed, 08 Apr 2020 10:56:36 GMT

I tried in different ways but finally I achieved, Below is the calculated dimension that worked for me

=If( %key_ID= '$(vDefaultkeyID)' AND [sheettype] = 1,Text(num(0)), aggr(sum(Overtime) *Sum(Rate)), Region,Team,EmpType,PName,TName)

below is the sample output I got.

Rate	Time	Cost
92.400000	36.000	3,326.400
0	9.000	0
0	9.000	0
0	9.000	0
92.400000	5.000	462.000
92.400000	0	0

topic Re: Calculated Dimension with Aggr and IF in QlikView

Calculated Dimension with Aggr and IF

Re: Calculated Dimension with Aggr and IF