Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@jyothish8807 try below
=sum(aggr(count(distinct total <Country>Shop), Country,MonthYear,Food))
HI,
Try this in frontend.
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
];
Any help guys 🙂
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)
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.
@jyothish8807 try below
=sum(aggr(count(distinct total <Country>Shop), Country,MonthYear,Food))
Thanks this seem to work 🙂