Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cariasojonjerom
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:

Capture1.PNG

Desired Result:

Capture.PNG

Thanks in advance

Tags (1)
1 Solution

Accepted Solutions
lech_miszkiewic
Honored Contributor III

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

12 Replies
lech_miszkiewic
Honored Contributor III

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

looking forward to your reply

cheers

cariasojonjerom
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

r_wroblewski
Contributor III

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.

test.png

Regards

Ronny

lech_miszkiewic
Honored Contributor III

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:

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

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

Capture.PNG

Regards,

Jon

cariasojonjerom
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

r_wroblewski
Contributor III

Re: Replace row dimension value using set analysis

Hi Jon

please use a "="sign inside variable.

test.png

Regards

Ronny

lech_miszkiewic
Honored Contributor III

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

cariasojonjerom
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?

Capture.PNG

Thanks and  Regards,

Jon