# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## Replace row dimension value using set analysis

Hi! I would like to ask if there's a way to change the value of a certain row dimension in a single pivot table using conditional calculations  (Set Analysis)?

Source Table:

Desired Result:

Tags (1)
1 Solution

Accepted Solutions
Partner

## Re: Replace row dimension value using set analysis

Sure,

Data Island is a detached table in data model which is not having any relationship with rest of model.

see picture

now If you would build your expression using CustomerId and Sum(Sales) with some set analysis it would be hard to show sum of Sales for Customer A on the line directly linked to Customer B. You would have to probably use AGGR Total function. Simpler solution is to create unique values just by loading them again with Load distinct... resident etc..

Thath creates dataisland which does not have direct relationship which means that you do not have to force anything against natural relationship.

see application attached

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
12 Replies
Highlighted
Partner

## Re: Replace row dimension value using set analysis

What are initial criteria you want to apply in order to chose row with different expression? Is this always 5th row or maybe 5th, 10th, 15 th etc. etc

Just to clarify:

You want to do this in:

- Pivot Table

- with 1 dimension

- where certain row will have different expression based on values from dimensions

cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
New Contributor

## Re: Replace row dimension value using set analysis

Hi Lech!

Thanks for replying,criteria would depend on the value of row dimension regardless of the row position

e.g. if(Dimension='Row4', Sum({\$<Dimension={'Row 1'}>}Measure),Sum(Measure)

Regards

Partner

## Re: Replace row dimension value using set analysis

Hi Jon

Yes, you can create a table like this.

I created a variable (vTEST) with "=Sum({\$<Dimension = {'Row_1'}>}Value)/Sum({\$<Dimension = {'Row_2'}>}Value)" and used "if( Dimension <> 'Row_5', Sum(Value), \$(vTEST))" as measure.

Regards

Ronny

Partner

## Re: Replace row dimension value using set analysis

Hi,

It all depends how your dimension will be linked with data. If there is connection between dimension and measure then going against a natural relationship may be difficult. What i did is i created dataisland field (Customer) based on my CustomerId field and then i used following formulas see picture:

table on left is having detached Customer dimension - not very efficient in terms of performance! Expression column shows you how values are calculated.

Table on right is just CustomerID (with Customer as a label) and sum(Sales)

Maybe it's not the most elegant solution but it works

cheers

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
New Contributor

## Re: Replace row dimension value using set analysis

Hi Ronny,

Thanks for replying. I tried the solution you have suggested but seems that i didn't arrive on the answer i was looking for. Already did create a variable with the same expression and incorporate it in the expression on the measures.

Regards,

Jon

New Contributor

## Re: Replace row dimension value using set analysis

Dear Lech,

Can you please tell me what a data island does and how to do this?

Regards,

Jon

Partner

Hi Jon

Regards

Ronny

Partner

## Re: Replace row dimension value using set analysis

Sure,

Data Island is a detached table in data model which is not having any relationship with rest of model.

see picture

now If you would build your expression using CustomerId and Sum(Sales) with some set analysis it would be hard to show sum of Sales for Customer A on the line directly linked to Customer B. You would have to probably use AGGR Total function. Simpler solution is to create unique values just by loading them again with Load distinct... resident etc..

Thath creates dataisland which does not have direct relationship which means that you do not have to force anything against natural relationship.

see application attached

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
New Contributor

## Re: Replace row dimension value using set analysis

Hi Ronny,

Thank you for this. Will this be applicable if i have added a dimension (Month, Year) and have the variable's value depend on the added dimension column?

Thanks and  Regards,

Jon