Add static dimension values with association in Pivot

[#1]

Hi All,

I have a Question related to a requirement of adding Static value along with Association in QlikView Pivot table.

Region	Sales
A	100
B	500
C	20
D	80
X	500

With reference to the above data, I have values A, B, C, D for Region and corresponding Sales coming from the database.

I want to add a new Region X (not present in database) whose sales is calculated based on other regions (For eg : X Sales = A+B-C-D = 100+500 - 20-80 = 500)

I want to have associative property intact, which means the values for X should be calculated dynamically based on filter selection.

For eg : If I choose Product O123 which is only in Region A, B, C..corresponding Sales figures should be used to calculate X Sales.

Product	Region	sales
O123	A	50
O123	B	100
O123	C	10

In above case, X sales = A+B-C-D = 50+100-10 -0 = 140.

Please advise how this can be done.

Thank you in Advance. 

[PradeepReddy · Accepted solution]

not understanding why you are considering 2 sales. I hope there would be a single sales measure only..

I tried to provide the solution as per my understanding..

see the attachment, it might be helpful..

[Gysbert_Wassenaar]

Create an extra table in the script to associate X with the regions:

LOAD * INLINE [

ReportRegion, Region, Factor

X, A, 1

X, B, 1

X, C, -1

X, D, -1

A, A, 1

B, B, 1

C, C, 1

D, D, 1

];

Then use ReportRegion as dimension instead of Region and multiply the Sales amount with Factor.

[#4]

Hi Pradeep

Thank you for the prompt response. It works, however we have numerous Regions and Measures.

Hence defining a condition for every combination of Measure and Region is cumbersome.

Is there a better way to do this. Appreciate your help in this matter.

[PradeepReddy]

then try the solution provide by gwassenaar‌ It would be generic..

[#6]

Hi Pradeep

With reference to the file you shared, when I select any value in Region (Eg: A) the table still shows up all Regions - A, B, C, X. How can we fix this, pls advise.

Thank you

[PradeepReddy]

see the attachment...

[#8]

Pradeep,

Thank you for the suggestion. Can we have value X in the list box too and the rows with Region X to populate only when X is chosen from the list box

Thank you

[PradeepReddy]

I hope it is not possible..