Sorting By Date In a chart while using the group by option

App Building
1

  • Goal: I want my chart to be sorted by date while grouping by another value

  • Steps: My data is displaying fine until I group by sales_rep then the dates get out of order .

  • Details: this is coming from a postgresql query that is grouping and sorting already and the results are in the correct order. This is the query you can see the output in the screenshots below.
    SELECT count(id), "salesRep", TO_CHAR(CAST(date_trunc ('week', es_deal."saleDate" ::date) as date),'MM/DD') as weekly,TO_CHAR(CAST(date_trunc ('week', es_deal."saleDate" ::date) as date),'yy') as year from es_deal where "organizationId"='01efbdc7-6dc4-4e40-bd0e-2284dba2a602' AND es_deal."saleDate" >
    NOW()::DATE-EXTRACT(DOW FROM NOW())::INTEGER-84 and "saleDate" <date_trunc('week', current_date) and "deletedAt" IS NULL group by year,weekly,"salesRep" order by year,weekly,"salesRep"

  • Screenshots: Here you can see it sorts the dates on the X axis fine

    image
    image1833×808 117 KB

    But as soon as i select group by sales rep, the dates on the x axis are no longer ordered correctly.
    image
    image1874×877 134 KB

  • App json export: if you need one i can try and figure this out but it would be alot of effort for me to clean it at this point as it is still mid development.

2

Anyone know how to fix it so these sort properly when grouped?

3

Hey @Justin_Posey! Welcome to the community. :slightly_smiling_face:

I'm pretty sure I know why you're seeing this particular behavior, but don't have a super simple fix. Essentially, grouping by a particular property causes the resulting data to be sorted according to that property, instead. In your case, for example, the 08/26 group gets bumped down in the order because it's missing a data point for Brandon Widner, who otherwise falls first alphabetically among all reps.

This means that one solution is to ensure that every rep has a record for each weekly period, even if their COUNT is 0. Depending on how your database is structured, this should be possible by tweaking the SQL query.

Alternatively, it looks like this works as expected using v2 of the component:

image
image838×650 18.3 KB

I'd probably recommend this approach! Let me know if you have any questions about it. :+1:

1 Like
4

Thank you for the tips!

since the query is counting the number of records per time period theres not really an easy way for me to have them come up with zeros for missing individuals since that would require creating a sale for them which would give them a 1 not a zero. Im not sure why it would auto sort by what were grouping by but I guessed that was what was going on.

The V2 you mentioned seems promising but im afraid I dont know what that actually means, how would I have it use v2 of the component? I dont see any versions in the component list..

All help is appreciated!

5

nevermind i got into the beta and am using the v2 component, it solved the issue and is also alot more flexible! thanks again for the tip!

1 Like