# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:  Contributor II

## Calculated Dimension with Aggr and IF

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

Labels (3)

• ### Script and Expressions

1 Solution

Accepted Solutions  Contributor II
Author

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  Contributor II
Author

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 Community Browser