topic Re: Sum Distinct and/or Aggr in App Development

Sum Distinct and/or Aggr

Hauglaen — Fri, 28 Jun 2019 11:09:49 GMT

Hello,

I have a dataset below. How do I sum only the transactions where the net price is between 0-50% of the Gross price?

Date	ID	Transaction number	Net price	Goss price	Sales channel	Price group	Copies sold
22.03.2019	14	2406637	69	381,65	01	01	4
28.01.2019	14	2359382	69	381,65	01	01	1
08.01.2019	14	2359368	235	381,65	01	01	1
03.01.2019	14	2359367	255	381,65	01	01	1
22.03.2019	15	2335733	69	381,65	01	01	4
28.01.2019	15	2321550	235	381,65	01	01	1
08.01.2019	15	2307368	235	381,65	01	01	1
03.01.2019	15	2293185	255	381,65	01	01	1

Desired sum for copies sold are 5 (ID 14) and 4 (ID 15).

I currently have:

if
(
sum({$< Price group ={'01'},Sales channel={'01'} >} Net price)/sum({$< Price group ={'01'},Sales channel={'01'} >} Goss price)<0.50
and
sum({$< Price group ={'01'},Sales channel={'01'} >} Net price)/sum({$< Price group ={'01'},Sales channel={'01'} >} Goss price)>0,
sum({$< Price group ={'01'},Sales channel={'01'} >} Copies sold)
)

Do I include Aggr and/or Distinct?

Much appreciated!

Re: Sum Distinct and/or Aggr

marcus_sommer — Fri, 28 Jun 2019 11:54:01 GMT

For me it looked that you don't need the whole summing-stuff and that the following should be enough:

sum({$< [Price group] = {'01'}, [Sales channel] = {'01'},
[Net price] = {"=[Net price]<[Gross price]*0.5"} >} Copies sold)

- Marcus

Re: Sum Distinct and/or Aggr

Hauglaen — Fri, 28 Jun 2019 12:22:58 GMT

Thank you!

I am displaying the the data in a table with only ID and Copies sold.

Your solution results in an empty table, but if I filter out an ID, the table shows the correct number of copies sold for that ID.

Do you have any suggestions?

Re: Sum Distinct and/or Aggr

marcus_sommer — Fri, 28 Jun 2019 12:30:36 GMT

In this case you need to enforce the check on the appropriate level, maybe with something like this:

sum(aggr(
sum({$< [Price group] = {'01'}, [Sales channel] = {'01'},
[Net price] = {"=[Net price]<[Gross price]*0.5"} >} Copies sold),
[Transaction number]))

- Marcus

Re: Sum Distinct and/or Aggr

Hauglaen — Fri, 28 Jun 2019 12:39:31 GMT

I get the same result.

Re: Sum Distinct and/or Aggr

kaanerisen — Fri, 28 Jun 2019 12:39:43 GMT

Hi Hauglaen,

You can achieve that by using simple set analysis if I get what you desire correctly.

sum({<[Transaction number]={"=[Net price]/[Goss price]<0.5"}>}[Copies sold])

If you have extra criterias you should add that to set analysis of expression.

Hope it helps..

Re: Sum Distinct and/or Aggr

marcus_sommer — Fri, 28 Jun 2019 12:49:06 GMT

Normally it should work. If the [Transaction number] isn't unique within the context of this calculation you might need to add further dimensions maybe the ID.

- Marcus