Sum Distinct and/or Aggr

[Hauglaen]

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!

[marcus_sommer]

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

[Hauglaen]

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?

[marcus_sommer]

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

[Hauglaen]

I get the same result.

[kaanerisen · Accepted solution]

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..

[marcus_sommer]

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