Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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..
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
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?
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
I get the same result.
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..
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