Data Relationships

[#1]

I have a pivot table showing a list of customers. The expressions give me values for Sales - GP - GP%.

Currently I need to add another column which encorporates an exchange rate. My problem is that these rates are being sucked in from a spreadsheet. (There is a different exchange rate for each month.)

When i bring in the exchange rate it only appears for the total values and there is a 0 value for each customer.

Does anyone know what i can do to have each customer's exchange rate showing?

Thanks

[#2]

My issue is that the exchange rate is not being applied to each customer in my pivot table.

As you said, there is no 1 exchange rate linked to your customer.

QlikView will sometimes find more than 1 value available for the same customer.

"More than 1 value" is displayed as "-" in a QlikView chart.

Even if it's the same value, this values occurs as many times as you have values.

Try to use

=only(ex_val)

in your chart.

This fonction will display the value if it's a single value that occurs more than 1 time.

If you still not have what you expect, use

=concat(ex_val, ', ')

in your chart.

This will display you all the values available for each customer. This will help you to understand what is wrong with your model.

Note that "concat(ex_val, ', ')" will display every possible values, whereas "concat(distinct ex_val, ', ')" will display every distinct possible values.

[#3]

Brian,

Can you post a table object with customer, month, exchange rate and value?

Rich

[#4]

Sorry for the delay in getting back to you. Here is a screenshot of the table structure.

I'm at a loss...

[#5]

Brian,

Is JJ_Date in DataParts a rename of the Invoice Date or the month of the invoice date?

If it is an invoice date does Sheet1$ contain one and only one representative date of every invoice date in your Invoice File or just a month end/begin date?

Does Sheet1$ contain multiple rates for the same date or are there missing dates within Sheet1$ that are in the invoice table?

Try this. Create a Table Object with Customer, Invoice Date, JJ_Date and ex_val to debug the association of the data between the tables.

This should help you see if there are missing and/or multiple JJ_Dates in your Sheet1$ table.

I suspect the problem lies in the data.

Rich

[#6]

Thanks Richard,

Is JJ_Date in DataParts a rename of the Invoice Date or the month of the invoice date?

In the code for DateParts I have... Date As invoice_date

                                                 Date As JJ_Date

If it is an invoice date does Sheet1$ contain one and only one representative date of every invoice date in your Invoice File or just a month end/begin date?

In Sheet1$ has a seperate date format again, It basically starts at Jan-07 and goes all the way to May-11. Each value has a unique rate.

Does Sheet1$ contain multiple rates for the same date or are there missing dates within Sheet1$ that are in the invoice table?

Only June & July 2011 have the same exchange rate. In my chart this is posing problems but have used the =only(ex_val) function to help this.

Yes, its definitely an issue with the data and the relationships, its been kind of trial and error so far trying to find a solution.

[#7]

Just to update the thread, I found a solution to this problem for anyone who is interested.

Basically I stored the exchange rate value in a variable (vExRate = ex_val) and it worked.

I'm now having some small issues with the fact that some months have the same exchange rates but i dont think it will take me as long to get to the bottom of that problem.

Thanks again for your help, much appreciated.