Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |