Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Jaeger
Contributor II
Contributor II

Get sales aggregation of country for each store

Hello,
I have the following datatable and I want to display a table with one row per store and get the sum of UnitSales for the store in one column and in a second column the sum of UnitSales for the country of the store.

ProductData:
LOAD * inline [
Store|Country|UnitSales|UnitPrice
Berlin|GER|4|16
Berlin|GER|3|14
Stuttgart|GER|10|15
Wien|AUT|9|9
Salzburg|AUT|5|10
Bern|SUI|2|20
Basel|SUI|25|25
] (delimiter is '|');

I tried something like Sum(TOTAL Aggr(Sum(UnitSales),Country, Store)) or Sum(TOTAL Aggr(Sum(UnitSales),Country)).

2 Replies
Usama
Creator
Creator

Hi,

 

If you are looking to achieve one line for every store and same for country in frontend, you can directly take straight table and put Dimensions/Measures. It will be shown as expected.

 

Usama_0-1640170967459.png

 

From Nothing - To Something - To Everything
Jaeger
Contributor II
Contributor II
Author

I found a solution with the following formula, but I hope there is a more elegant/dynamic way:
if(Country='GER', Sum({$< Country={'GER'} >}TOTAL Aggr(Sum(UnitSales),Country)),
if(Country='AUT', Sum({$< Country={'AUT'} >}TOTAL Aggr(Sum(UnitSales),Country)),
if(Country='SUI', Sum({$< Country={'SUI'} >}TOTAL Aggr(Sum(UnitSales),Country)),
Sum(TOTAL Aggr(Sum(UnitSales),Country)))))

table with sample valuestable with sample values