topic Re: Add static dimension values with association in Pivot in QlikView

Add static dimension values with association in Pivot

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Add static dimension values with association in Pivot

PradeepReddy — Mon, 14 Mar 2016 09:51:43 GMT

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

Re: Add static dimension values with association in Pivot

Gysbert_Wassenaar — Mon, 14 Mar 2016 09:56:21 GMT

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.

Re: Add static dimension values with association in Pivot

Mon, 14 Mar 2016 10:09:35 GMT

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.

Re: Add static dimension values with association in Pivot

PradeepReddy — Mon, 14 Mar 2016 10:14:48 GMT

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

Re: Add static dimension values with association in Pivot

Tue, 15 Mar 2016 08:58:57 GMT

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

Re: Add static dimension values with association in Pivot

PradeepReddy — Tue, 15 Mar 2016 12:26:21 GMT

see the attachment...

Re: Add static dimension values with association in Pivot

Wed, 16 Mar 2016 04:34:59 GMT

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

Re: Add static dimension values with association in Pivot

PradeepReddy — Wed, 16 Mar 2016 05:07:34 GMT

I hope it is not possible..