Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
salonibhatia
Contributor III
Contributor III

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

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:

RegionRegion_a SalesRegion_b SalesDifference
A2030 
B4010 
C6030 
D7070 
E6560 
F55- 

 

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!

3 Replies
salonibhatia
Contributor III
Contributor III
Author

@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 

sunny_talwar

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.

salonibhatia
Contributor III
Contributor III
Author

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