Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
femi_owoseni
Contributor III
Contributor III

Alternative to If function in dimension for Pivot table

Hi,

I am using this expression as a dimension in a pivot table.

if([ChangeDate]=vLatestDate,[BusinessTitle] & ' (' & [Business Code] & ')',)

then I suppress Nulls and zeros and the results are as expected.

The issue is, the pivot table is now really slow and frequently times out. 

Is there another expression that will yield faster results?

PS. I am unable to make changes to the data model using the load editor at this time.

 

 

 

 

1 Solution

Accepted Solutions
sunny_talwar

If all your expressions have the set analysis to include the most recent change date, then your dimension will automatically show the value of the most recent change date. Basically, only those dimensions which meet the criteria of your expression are usually shown... is this not what you are seeing? Would you be able to share a sample to show your issue?

View solution in original post

4 Replies
sunny_talwar

1) May be use set analysis instead of if statement here

Dimension

[BusinessTitle] & ' (' & [Business Code] & ')' 

Expression

Sum({<ChangeDate = {"$(=vLatestDate)"}>}Measure)

Note: The expression may not still work because I am not sure what is behind vLatestDate. May be you will need to add formatting to the variable so that it can be compared to ChangeDate field's format. But the idea stays the same that instead of using an if statement in your calculated dimension... use set analysis.

2) Create a new field in the script [BusinessTitle] & ' (' & [Business Code] & ')' -> since you mentioned that you don't want to make script changes, so this can be hold off until you are ready for script changes.

femi_owoseni
Contributor III
Contributor III
Author

The expression is fine and works okay

My Expression: Min({<[Change Date]= {$(=chr(34))$(vLatestDate)$(=chr(34))}, [Country]={"United States"}>}[Total Sales]).

The dimension however needs to only include the most recent [Business Code] as well. The if statement I posted works but it is slow.

 

sunny_talwar

If all your expressions have the set analysis to include the most recent change date, then your dimension will automatically show the value of the most recent change date. Basically, only those dimensions which meet the criteria of your expression are usually shown... is this not what you are seeing? Would you be able to share a sample to show your issue?

femi_owoseni
Contributor III
Contributor III
Author

Ok I understand a little better now. I applied the same criteria on the other measures in the pivot table and it worked fine. No need for the if statement.