Set analysis

[#1]

Hi Folks, I have a strange problem with my chart expression using set analysis. I have described my problem in the attachment.

Thanks for your time!

[jagan]

Hi,

Use this expression for Domestic :

=SUM(AGGR(if(PLNT_CNT_CD = SUPP_CNT_CD,Sum(AMOUNT_USD), 0), linkKey3))

Use this expression for Import:

=SUM(AGGR(if(PLNT_CNT_CD <> SUPP_CNT_CD,Sum(AMOUNT_USD), 0), linkKey3))

I found some data issue in your file which is highlighted

Regards,

jagan.

[ToniKautto]

Sorry to say it, but you are not using set analysis in the example. Please see Set Analysis in manula for references on the set analysis syntax and functionality. However, in this case it looks like you might use IF statements successfully, so no need to change if it works out.

In your chart you are calculating with fields in three different tables. By making a limitation in the data with the IF statement you likely create unlogical links in the data which will be represented by NULL. Since QlikView can not find a logical link for the values it will multiply the value with all other values as a best effort approach. This type of NULL relations is a common root cause when values get much larger then the expected one, since it is a multiplier of the expected value. The same sympthoms commonly appear when calculating over data models that have synthetic keys.

By visualizing the zero values in the chart you will see that there is a NULL representation on the PLANT_CODE dimension, which does not exist in the the other chart.

Chart Properties > Presentation > Suppress Zero-Values

EDIT:

As a side note I am not convinced that the expression above will give you the correct answer either, since it does not give exactly the same value as your 'correct chart'. Please evaluate the datat model and make sure it is logically correct for your required calculations.

[#4]

Hi Jagan,

Could you please attach the file itself as I cannot see the screenshot clearly even after zooming?

Thanks

[#5]

Toni, thanks for pointing out... but I could not get this correct using set analysis and then I had put it in a simple IF statement which I thought should work...

I have already taken care of the NULL value suppression. Let me know if you see anything wrong with the data model...

[#6]

Hi Jagan,

I could see your comment after viewing it as PDF. Well... the issue you have mentioned is the actual fact and its correct. Basically Plant code is factory code and plant country is country in which plant is located and supplier country code is country from which the materials has been supplied to that plant. So plant country code could be same as supplier country code or different or both. Ex. -  any manufacturing plant gets its raw material supplies from within the country or outside as well.

Thanks,

Archana

[jagan]

.

I am unable to upload the QVW file, because of restrictions, but I attached individual images for each chart.

Regards,

Jagan.

[#8]

As I said.. Its correct and there is no issue with it

[#9]

Hi,

Is there any1 who can help me figure out this??

Really appreciate your time!!

Thanks

[#10]

Hello Archana.

As far that I could notice, you have a strange issue with the relationship in the tables of these two fields: PLNT_CNT_CD and AMOUNT_USD (Master_Plant_List and SPEND_TABLE).

You can test and see what I'm trying to explain if you create a text object with this expression:

=Money(Sum(if(PLNT_CNT_CD='US', AMOUNT_USD, 0)))

This will not work and you will get a $23,478,210.67 ...

If you alter the expression to

=Money(Sum(if(SUPP_CNT_CD='US', AMOUNT_USD, 0)))

You will get the correct result... that indicates that the relationship of the tables Supp_Daily and SPEND_TABLE is correct.

So, I Suggest to review the part of the script that creates and llink the SPEND_TABLE and Master_Plant_List.

Regards,

Bruno Oliveira