Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

femi_owoseni
New 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

Re: Alternative to If function in dimension for Pivot table

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

Re: Alternative to If function in dimension for Pivot table

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
New Contributor III

Re: Alternative to If function in dimension for Pivot table

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.

 

Re: Alternative to If function in dimension for Pivot table

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

femi_owoseni
New Contributor III

Re: Alternative to If function in dimension for Pivot table

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.