How to make lookup (aggregation) work in charts

[data_data]

Hello, i am having a hard time trying to make this column bar chart. i trying to compare the number of customers who registered ( from Users collection) against the number of those customers who actually made an order ( from Order collection) by date (month or year)

now in mongodb charts i cannot use a query or aggregation that contain lookup. i tried to use the lookup field thing but it is not showing the correct results.

[tomhollander]

Hi @data_data -

While you can’t enter a $lookup stage directly in the query bar, the Lookup Field option does the same thing (you can see what pipeline it generates on the […] menu at the top right). I can’t tell what you did wrong with the Lookup Field option without more detail, but possibly you selected the incorrect fields. Feel free to share more info.

If you do want to use $lookup directly, you can do this by putting the pipeline into a Charts View and building a chart from that.

Tom

[data_data]

thanks for the respond.

i did try using the lookup field and managed to get the “requests” collection to “users” collection, but when i use the column chart to compare the count of registered customers against the count of those same customers who actually made a requests, i get incorrect results. i may be doing the wrong way, thats why im asking if i can doing it by aggregating it.

" you can do this by putting the pipeline into a Charts View and building a chart from that. " i have looked for this and couldnt figure where to find it. i am not the project owner by the way, just a dashboard owner

[tomhollander]

I’m referring to this command in the chart builder.

[data_data]

ok, but i cant edit it!

or you mean to copy it and add the lookup function? but then where do i run it?

[tomhollander]

Sorry you are correct that you can’t edit it; this is mainly useful for diagnostics if you aren’t sure what query is being executed for your chart. In the case of your Lookup Field, it lets you see the underlying $lookup stage in the pipeline, and if it’s not as expected it may give you clues as to what you need to change in the UI.