topic Re: Sum Distinct Values in Table Total in App Development

Sum Distinct Values in Table Total

waleeed_mahmood — Wed, 22 Sep 2021 17:35:13 GMT

Hello,

I have a question that i cant seem to solve.

I have the following table:

Region Coverage	Sales Employee	Targets
East	user1	1,350,000
East	user2	1,350,000
East	user3	1,350,000
East	user4	1,350,000
Total		5,400,000

Since, the region is the same and the targets are the same, can we have the total to be 1,350,000 since all targets in the regions are the same?

Thank you in advance.

Waleed

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Wed, 22 Sep 2021 18:47:37 GMT

Try this out: Sum(Aggr(Sum(Targets),[Region Coverage]))

Sum(total <[Region Coverage]> Targets)

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Wed, 22 Sep 2021 19:03:55 GMT

Hi Thiago,

It doesnt work. It gives 5400000 to one user and makes rest 0. Also, i do have other regions in this which have targets of their own and not same at all.

Thanks

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Wed, 22 Sep 2021 19:08:09 GMT

Check: Sum(Aggr(Sum(Targets),distinct [Region Coverage]))

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Wed, 22 Sep 2021 19:13:18 GMT

Hello,

This turns it all 0.

Thanks

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Wed, 22 Sep 2021 19:18:56 GMT

Are you able to share a mockup of your data?

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Wed, 22 Sep 2021 19:25:02 GMT

Hello thiago,

Unfortunately, the data is confidential and in a vast amount. all other regions are okay. But East we have it set up differently and we need to add it only once.

This is the following table with all users:

Region Coverage	Users	Target
Total		7,300,000
Central	Total	1,250,000
User1	400,000
User2	25,000
User3	75,000
User4	300,000
User5	200,000
User6	0
User7	250,000
East	Total	5,400,000
User8	1,350,000
User9	1,350,000
User10	1,350,000
User11	1,350,000
West	Total	650,000
User12	350,000
User13	100,000
User14	200,000

Thanks.

Re: Sum Distinct Values in Table Total

Jflori4n — Wed, 22 Sep 2021 19:55:28 GMT

Hi Thiago,

Try with -> sum({<Users={'Total'}>} Target)

However, I recommended normalized your data

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Wed, 22 Sep 2021 19:57:47 GMT

Here it is:

Sum(total <[Region Coverage]> distinct Targets)

The output:

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Thu, 23 Sep 2021 15:32:18 GMT

Hi Thiago,

Is it possible you could try this with the larger data set i provided above? I have tried the solution above and it is giving me random numbers again. Please note that i have other conditions in my set analysis as well. Also, Shouldnt the set analysis have {} around the <> symbols? so it becomes

sum(total {<[region Coverage]>} distinct Targets)

Please let me know.

Thank you

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Thu, 23 Sep 2021 17:08:33 GMT

This is the output I have:

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Thu, 23 Sep 2021 17:14:03 GMT

Hi,

Do you mind sharing the QVF file?

Thank you

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Thu, 23 Sep 2021 17:19:32 GMT

Here is the script I've used:

Temp:
LOAD * inline [
Region Coverage, Users, Target
Central,User1, 400000
Central,User2, 25000
Central,User3, 75000
Central,User4, 300000
Central,User5, 200000
Central,User6, 0
Central,User7, 250000
East,User8, 1350000
East,User9, 1350000
East,User10, 1350000
East,User11, 1350000
West,User12, 350000
West,User13, 100000
West,User14, 200000
];

Re: Sum Distinct Values in Table Total

sunny_talwar — Thu, 23 Sep 2021 17:42:06 GMT

You might be able to use this

Aggr(NODISTINCT Sum(DISTINCT Target), [Region Coverage]))

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Thu, 23 Sep 2021 17:43:52 GMT

Great @sunny_talwar!!

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Thu, 23 Sep 2021 17:48:01 GMT

Hello Sunny, Thiago,

Using your script and expressions, this is what i get.

Except East, other regions have their own targets that should appear. Only east is the exception.

Thank you.

Re: Sum Distinct Values in Table Total

sunny_talwar — Thu, 23 Sep 2021 17:51:27 GMT

I am not entirely sure what you need, but may be this

If([Region Coverage] = 'East', Aggr(NODISTINCT Sum(DISTINCT Target), [Region Coverage])), Sum(Target) )

Re: Sum Distinct Values in Table Total

waleeed_mahmood — Thu, 23 Sep 2021 17:56:43 GMT

Hi Sunny,

Ill try your expression but please see below for desired result:

Thank you

Re: Sum Distinct Values in Table Total

Thiago_Justen_ — Thu, 23 Sep 2021 18:02:52 GMT

If(Dimensionality()=0,Sum(Targets),Sum(total <[Region Coverage]> distinct Targets))

Re: Sum Distinct Values in Table Total

sunny_talwar — Thu, 23 Sep 2021 18:06:19 GMT

I am guessing Current Result uses Sum(Target)... if it does, then I think this should work

If([Region Coverage] = 'East',
    Aggr(NODISTINCT Sum(DISTINCT Target),  [Region Coverage])),
    Sum(Target)
)