topic Sum if a value matches in Pivot Table using an unrelated Dimension in QlikView

Sum if a value matches in Pivot Table using an unrelated Dimension

salonibhatia — Sat, 16 Nov 2024 18:37:24 GMT

Hi,

I have a transaction table, using 2 different dimensions say Region_a, Region_b of that transaction I have created a new dimension say Region.

This new Region column is loaded as Inline(there could be a better way) script as follows:

Load Region_a

Resident Transaction;

Concatenate

Load Region_b

Resident Transaction;

The reason for doing this is that I need to create a Pivot Table which needs to have all the Regions and the Pivot table needs to be like below:

Region	Region_a Sales	Region_b Sales	Difference
A	20	30
B	40	10
C	60	30
D	70	70
E	65	60
F	55	-

Difference can be calculated once am able to calculate Sales. Is there anyway using Set Analysis I can write that if Region matched Region_a then give me sales or any other better method. I have tried Sum (if), Set analysis stating Region=Region_a but none of them are working. Strange thing sum(if) is not giving me correct data for all the regions. Any help would be great!

Thanks in advance!

Re: Sum if a value matches in Pivot Table using an unrelated Dimension

salonibhatia — Tue, 19 May 2020 07:28:04 GMT

@swuehl , @sunny_talwar Any suggestions? I have found a fix by making Region columns as per the specific concatenated tables in the transaction but am not that happy with this fix, looking for a better solution. Please help

Re: Sum if a value matches in Pivot Table using an unrelated Dimension

sunny_talwar — Tue, 19 May 2020 11:30:46 GMT

Hi, I am not entirely sure I understand what you are trying to do. Would you be able to share the solution that you implemented which might give idea as to what you are trying to do.

Re: Sum if a value matches in Pivot Table using an unrelated Dimension

salonibhatia — Wed, 27 May 2020 13:02:12 GMT

@sunny_talwar Apologies for the late reply! Even though all the transaction tables are concatenated, there are some tables having salesamount for Region_a mapping and others having sales of region_b, so I created a common field region as below:

Transaction:

Load*,Region_a as region

Resident xyz.qvd;

concatenate

Load *,Region_b as region

resident gef.qvd;

I did this for all the tables concatenated and then I used the sum as required.

Earlier, I tried creating an island table which concatenated all the regions. After that I was trying to use sum(if) but that didn't work. Luckily the tables were little different and this method worked but, there would definitely be a better way.

I also tried linking that data island but since some tables had Region_a and other had Region_b, it didn't work.

Hope this explains!