Problem with AvgCost expression

[Michael_Chappell]

Good Afternoon All,

As we have a UK Mainland and an International team it's important that we work with the Transactional Currency in some instances. I'm trying to calculate the AvgCostAmount for our International team by using the expression: Sum(AverageCostAmount)*ExchangeRate which works great when only selecting one month however when looking at a period greater than one month it only returns a hyphen.

We work to one budgeted Exchange Rate per month and my assumption is that Qlik isn't liking that there are multiple Exchange Rates when looking at larger periods. Is there an expression that I can use for Qlik to look at the Exchange Rate for each given month when calculating the AvgCostAmountTC?

All help is appreciated!

[GaryGiles]

I think you need to use the Aggr() function to first aggregate the values for each month.  Try this:

sum(aggr(sum(AverageCostAmount)*ExchangeRate, [Month]))

[Michael_Chappell]

Hi Gary,

Thank you for your reply. The above expressions works, however all values are coming back as '0.00'.

[GaryGiles]

And we are certain that there is only one ExchangeRate per [Month] and [Month] is the right field name?  We may want to add the Only() function to our ExchangeRate.

sum(aggr(sum(AverageCostAmount)*Only(ExchangeRate), [Month]))

If you create a table that has [Month] as the dimension and have 2 different measures, one: sum(AverageCostAmount), two: Only(ExchangeRate); what do the results look like?

[Michael_Chappell]

Hi Gary,

There  is definitely only one Exchange Rate per Month (or InvoiceMonth as the field is actually called. I modified your expression to reflect this).

Unfortunately adding the Only() function still results in zeros.

Creating said table, the ACA reacts as it should however the Only(ExchangeRate) results in '-' across all months.

[GaryGiles]

Try changing the Only(ExchangeRate) in the table to Concat(Distinct ExchangeRate,',').  What results do you get for each month?

[Michael_Chappell]

Each cell is now populated with a concatenation of the three Exchange Rates (Jan = ER1, ER2, ER3).

[GaryGiles]

We’re missing something here.  I thought there is one value per month and it should be numeric.  Why are there multiple values for a month?

[Michael_Chappell]

The values are appearing as a numeric, I used ER1-3 as an example of how the data is appearing. My apologies for not specifying.

As for the multiples per month, we work in several currencies although there is only one budgeted exchange rate per currency i.e EURO will only have one value, USD will have a different single value for the month.

[GaryGiles · Accepted solution]

Good information to have!!  Is the Currency field assoicated with the [Month] field?  We need to add the currency field to the expression:

sum(aggr(sum(AverageCostAmount)*Only(ExchangeRate), [Month],[Currency]))

Just curious, what results do you get by simply using the following expression?

sum(AverageCostAmount*ExchangeRate)