topic Re: Problem with AvgCost expression in App Development

Problem with AvgCost expression

Michael_Chappell — Wed, 16 Jun 2021 11:24:09 GMT

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!

Re: Problem with AvgCost expression

GaryGiles — Wed, 16 Jun 2021 12:47:28 GMT

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]))

Re: Problem with AvgCost expression

Michael_Chappell — Wed, 16 Jun 2021 13:02:27 GMT

Hi Gary,

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

Re: Problem with AvgCost expression

GaryGiles — Wed, 16 Jun 2021 13:23:19 GMT

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?

Re: Problem with AvgCost expression

Michael_Chappell — Wed, 16 Jun 2021 14:30:12 GMT

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.

Re: Problem with AvgCost expression

GaryGiles — Wed, 16 Jun 2021 14:58:54 GMT

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

Re: Problem with AvgCost expression

Michael_Chappell — Wed, 16 Jun 2021 15:32:15 GMT

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

Re: Problem with AvgCost expression

GaryGiles — Wed, 16 Jun 2021 16:00:33 GMT

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?

Re: Problem with AvgCost expression

Michael_Chappell — Thu, 17 Jun 2021 10:10:27 GMT

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.

Re: Problem with AvgCost expression

GaryGiles — Thu, 17 Jun 2021 11:44:12 GMT

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)

Re: Problem with AvgCost expression

Michael_Chappell — Thu, 17 Jun 2021 12:41:41 GMT

Sorry Gary, I should've given that information from the get go!

Your first expression is still returning zeros unfortunately however I tried the bottom one and (you're not going to believe this Gary) but moving the bracket seems to have sorted the issue! I'm sure I tried that expression at the beginning when testing and it returned blanks but for whatever reason it's now working perfectly!

Thank you so much. You're a Qlik Genius!