topic Retaining a Total Value in a Calculation in App Development

Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 13:09:18 GMT

Hi I'm trying to fix a total to then display my selections as a %.

So the challenge is I have set analysis as follows:

=(sum({$<USAGE_CHARGE_GROUP= {'VOICE'} >} ACTUAL_DURATION)/60)

Which gives me the total minutes where the USAGE_CHARGE_GROUP = Voice. Want I want to be able to do is make a selection e.g. Calls to Italy and then the selection number of minutes to be divided by the Total minutes to show the % of total minutes that were calls to Italy.

I'm unable to get this to work.

Hope this is clear?

Thanks in advance.

Kevin

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 13:13:59 GMT

What is the name of the field where you select Calls to Italy? May be you need this (assuming the field is called CallLocation)

=(Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}, CallLocation>} ACTUAL_DURATION)/60)

Re: Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 13:44:04 GMT

Thanks for the prompt reply Sunny. I think that exhibits the same behaviour that I'm currently seeing.

My dashboard shows the total number of minutes and when I select the "Call location" it obviously updates the minutes in context to the selection made, in a separate KPI I want to express the number of minutes for the "Call Location" as a % of the overall minutes. e.g. Calls to Italy 1,000 minutes, overall minutes = 10,000 therefore 10% of the minutes are calls to Italy.

What I can't figure out is a way to retain the overall minutes so I can use that as denominator.

Kevin

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 13:50:48 GMT

So, if this is in a KPI, something like this should work....

Here, I am assuming that you will be looking at this by marking selection in CallLocation field.

=(Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/60)/
 (Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}, CallLocation>} ACTUAL_DURATION)/60)

Numerator should filter based on the selection in CallLocation, but the denominator should ignore selection in CallLocation and should show the ACTUAL_DURATION from all locations. Isn't that what you want?

Re: Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 13:56:42 GMT

Yes that's what I'm trying to achieve Sunny - I need to try that out so will get back to you when I get a chance to have a look

Re: Retaining a Total Value in a Calculation

PrashantSangle — Thu, 07 Feb 2019 15:18:53 GMT

Try with TOTAL keyword. Also you don't required to divide by 60 while calculating %
Try below

=Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/
Sum(TOTAL <CallLocation> {$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)

Regards,
Prashant Sangle

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 15:21:25 GMT

@PrashantSangle wrote:
Try with TOTAL keyword. Also you don't required to divide by 60 while calculating %
Try below

=Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/
Sum(TOTAL <CallLocation> {$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)

Regards,
Prashant Sangle

TOTAL in a KPI object? How is that going to help in a dimensionless KPI object? or did I miss the part that this was needed in a table? 🙂

Re: Retaining a Total Value in a Calculation

PrashantSangle — Thu, 07 Feb 2019 15:33:42 GMT

I think i missed out that he required it in KPI . You are correct in this case he have to nullify the location field.

But you don't required to divide it by 60 in both numerator and denominator.

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 15:35:41 GMT

@PrashantSangle wrote:
But you don't required to divide it by 60 in both numerator and denominator.

Agreed 🙂

Re: Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 15:44:58 GMT

Thanks Guys - the dividing by 60 is turning seconds into minutes

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 15:59:48 GMT

Ratio is not in minutes or seconds, ratio is a ratio and that is why you don't need 60 🙂

Re: Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 16:01:57 GMT

Yes of course

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 16:13:42 GMT

Okay so where do we stand? Do we have a solution yet 🙂

Re: Retaining a Total Value in a Calculation

kevinstanfield — Thu, 07 Feb 2019 16:16:18 GMT

I'm going to take a look this evening Sunny so will update tmrw - thanks

Re: Retaining a Total Value in a Calculation

sunny_talwar — Thu, 07 Feb 2019 16:18:14 GMT

Sorry make sense. Let us know what you find.

Re: Retaining a Total Value in a Calculation

kevinstanfield — Fri, 08 Feb 2019 10:06:14 GMT

That's worked nicely guys - thanks, appreciated.

Just in terms of describing this behaviour to colleagues am I right in saying that any field added after the set analysis condition is set i.e. those in red will be excluded from the set analysis and that many fields could be added?

Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}, CALL_CLASS,USAGE_CALL_GROUP>} ACTUAL_DURATION)

Kevin

Re: Retaining a Total Value in a Calculation

kevinstanfield — Fri, 08 Feb 2019 10:07:05 GMT

Accepted as a solution

Re: Retaining a Total Value in a Calculation

sunny_talwar — Fri, 08 Feb 2019 12:16:46 GMT

@kevinstanfield wrote:
those in red will be excluded from the set analysis
Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}, CALL_CLASS,USAGE_CALL_GROUP>} ACTUAL_DURATION)

These fields in Red will ignore selection for the particular expression they are used in. If this is what you meant, then yes you are right 🙂

@kevinstanfield wrote:
that many fields could be added?

That is correct, you can ignore selection in as many fields as you would like. If you want to ignore selection in all fields, then use 1 instead of naming all the fields

Sum({$<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)/
Sum({1<USAGE_CHARGE_GROUP= {'VOICE'}>} ACTUAL_DURATION)