More than one aggregate calculation in one expression

Braveen — Mon, 29 May 2023 10:56:42 GMT

My data looks like below snip. Granularity is at Company, Monthstart and City, while showroom is needed for other pieces of the app, Sales is attributed to the showrooms within the city .

Desired output looks like below;

Table view which shows all the companies, sales and market share:

Without any filter view

When showroom filter is applied;

The expression should take care of 3 scenario:

1. Without any filter, sales must be calculated for unique Company, City, Monthstart combination

2. When showroom filter is applied across different cities like Delhi-1 and Mumbai-1, again sales must be calculated for unique Company, Monthstart combination and respective showrooms

3. When two or more showrooms in same city is selected like Delhi-1 and Delhi-2, sales must be calculated for unique Company, City, Monthstart combination

I tried

1. Not sure how to achieve this

2. sum( {<Monthstart= {'2023-05-01'}>} Registration) /*Current month*/ , Need help for overall sales

3. sum( {<Monthstart= {'2023-05-01'}>} Registration) / count(distinct Showroom)

Help would be appreciated.

Here is the inline script :);

LOAD * INLINE [ Company, City, Showroom, Monthstart, Sales A, Delhi, Delhi-1, 01/Apr/23, 326 A, Delhi, Delhi-2, 01/May/23, 326 A, Mumbai, Mumbai-1, 01/Apr/23, 272 A, Mumbai, Mumbai-2, 01/May/23, 272 A, Goa, Goa-1, 01/May/23, 61 B, Delhi, Delhi-4, 01/Feb/23, 608 B, Delhi, Delhi-2, 01/Mar/23, 608 B, Delhi, Delhi-3, 01/Apr/23, 608 B, Delhi, Delhi-1, 01/May/23, 608 C, Kochi, Kochi-1, 01/Apr/23, 815 C, Bengaluru, Bengaluru-1, 01/May/23, 673]

topic More than one aggregate calculation in one expression in App Development

More than one aggregate calculation in one expression