Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brucemazynzolt
Contributor II
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

RateTimeCost
92.40000036.0003,326.400
92.4000005.000462.000
09.000782.910
09.000782.910
09.000120.420

 

Need to achieve like below table

RateTimeCost
92.40000036.0003,326.400
09.0000
09.0000
09.0000
92.4000005.000462.000
92.40000000

 

Kindly help me to achieve 

1 Solution

Accepted Solutions
brucemazynzolt
Contributor II
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.

RateTimeCost
92.40000036.0003,326.400
09.0000
09.0000
09.0000
92.4000005.000462.000
92.40000000

 

View solution in original post

1 Reply
brucemazynzolt
Contributor II
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.

RateTimeCost
92.40000036.0003,326.400
09.0000
09.0000
09.0000
92.4000005.000462.000
92.40000000

 

View solution in original post