Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jyothish8807
Master II
Master II

Aggregation

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 YearFoodTotal Shops
12021Apple2
12021Mango2
12021Orange3
12021Grapes3
12021Banana3

@jagan @sunny_talwar 

Best Regards,
KC
1 Solution

Accepted Solutions
Kushal_Chawda

@jyothish8807  try below

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

View solution in original post

7 Replies
kaushiknsolanki

HI,

Try this in frontend.

kaushiknsolanki_0-1625721264824.png

 

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
jyothish8807
Master II
Master II
Author

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
];

 

Best Regards,
KC
jyothish8807
Master II
Master II
Author

Any help guys 🙂

Best Regards,
KC
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
Master II
Master II
Author

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

Best Regards,
KC
Kushal_Chawda

@jyothish8807  try below

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

View solution in original post

jyothish8807
Master II
Master II
Author

Thanks this seem to work 🙂

Best Regards,
KC