Aggregation

[jyothish8807]

Hi All,

I am trying to create a line chart with MonthYear and Food as dimension, and a measure to get total no shops with in the country where the Food belongs to. below is the sample data. I want to do this in front end, as i cant duplicate the data in backed due to data volume.

Load * Inline [
Country, Food, Qty, Shop, MonthYear, Type
IND, Apple, 100, S1, 012021, A
IND, Apple, 100, S1, 012021, B
IND, Mango, 200, S2, 012021, A
ESP, Orange, 400, S3, 012021, C
ESP, Grapes, 500, S4, 012021, E
ESP, Banana, 200, S5, 012021, D
];

expected Output:

Month Year	Food	Total Shops
12021	Apple	2
12021	Mango	2
12021	Orange	3
12021	Grapes	3
12021	Banana	3

@jagan @sunny_talwar 

[kaushiknsolanki]

HI,

Try this in frontend.

[jyothish8807]

Hi Kaushik,

Thank you for your reply, unfortunately I cannot use Country as my Dimension, its only MonthYear and Food in my case in a line chart.

Moreover i will have to use distinct always as same shop might have multiple entries, below is an updated sample data.

Load * Inline [
Country, Food, Qty, Shop, MonthYear, Type
IND, Apple, 100, S1, 012021, A
IND, Apple, 100, S1, 012021, B
IND, Mango, 200, S2, 012021, A
ESP, Orange, 400, S3, 012021, C
ESP, Grapes, 500, S4, 012021, E
ESP, Banana, 200, S5, 012021, D
];

[jyothish8807]

Any help guys 🙂

[avinashelite]

One question what if the food is spread across multiple countries ? in that case without country dimension  how you will be able to show the shop count ?

Aggr(Count( DISTINCT Shop),Country,Food)

[jyothish8807]

Exactly that is the challenge, its possible that "Apple" is available in "ESP" for another "shop 4" in that case the count will become "5" for Apple as there is total of 5 distinct shops part of IND and ESP.

I have fixed this in backend but the solution increased the app size tremendously due to cross joins. 

[Kushal_Chawda · Accepted solution]

@jyothish8807  try below

=sum(aggr(count(distinct total <Country>Shop), Country,MonthYear,Food))

[jyothish8807]

Thanks this seem to work 🙂