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: 
Anonymous
Not applicable

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:

Capture1.PNG

Desired Result:

Capture.PNG

Thanks in advance

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Sure,

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

see picturemodel.PNG

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..

script.PNG

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.

View solution in original post

12 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

looking forward to your reply

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.
Anonymous
Not applicable
Author

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

r_wroblewski
Partner - Creator III
Partner - Creator III

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.

test.png

Regards

Ronny

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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:

Capture2.PNG

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.
Anonymous
Not applicable
Author

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.

Capture.PNG

Regards,

Jon

Anonymous
Not applicable
Author

Dear Lech,

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

Regards,

Jon

r_wroblewski
Partner - Creator III
Partner - Creator III

Hi Jon

please use a "="sign inside variable.

test.png

Regards

Ronny

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Sure,

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

see picturemodel.PNG

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..

script.PNG

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.
Anonymous
Not applicable
Author

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?

Capture.PNG

Thanks and  Regards,

Jon