EXO - Analysis code report grouping stops working when report starts after 29th Nov

Hi Shadoxity ,

Those kinds of problems are usually caused by your Groups on the report not matching your Order Bys in the SQL Data Pipeline.

Unlike setting a Data Pipeline grouping (which applies to all data before display), Clarity applies its' Group By to the data as it is given it for display to screen.

This means you can get quite different results, depending on if you either:

1. Group and Order all data in the Data Pipeline (SQL Query)
2. Order all data in the Data Pipeline (SQL Query), and group in Clarity.
3. Group some data in the pipeline (SQL Query), and do additional grouping in Clarity

You'll notice the reports in Exo are a mix.  One thing to be aware of is that Failing to specify an Order by will use "Native" Ordering, which usually looks kind of like SEQNO order (the order transactions are recorded in), but which actually can't be trusted to be consistent in larger datasets. (Due to the way SQL does its magic behind the scenes.

I've tried to illustrate this quickly with some screenshots from Excel.  

Consider, a table with 6 records, and 4 groups:

Records on left, what SQL does in middle, what Clarity would do on the end.

So you can see, you get fully different grouping results if you don't specify an Order by, and just rely on Clarity grouping.

(Performance Tip: Clarity data processing uses more memory than using SQL data grouping, and will be slower.  Where possible, the best performance will always come from doing all of your data manipulations in the data pipeline/SQL stage, and just using Clarity to display.  For this reason, I mostly use Clarity grouping to collect together lines when I need to display Detail information as well underneath the groups.)

You can specify these options in Clarity by:

• Using the Group (funnel icon) in the data pipeline:
  Using the Group option in the data pipeline gives you full control over what SQL does.
• Using the Calcs tab in the data pipeline
  Usually you can avoid using the GROUP option, just by using the CALCS tab. Clarity will automatically fill in the GROUP tab.
• Using the SORT tab in the data pipeline:
  Using the Sort/Order By option gives you the ability to control which groups Clarity applies.

I strongly recommend setting specific Order By's for reports, even when using SQL Calculations/Groups to add up your data.  If you don't set an Order By, the data just might decide to come out in a different order (Because SQL does whatever is fastest, and sometimes that's not very intuitive.)

I think this covers your question and should give you enough to get your report working properly.  If you need further help I strongly recommend talking to your Business Partner, as these kinds of issues are a very extensive area that most businesses find it worthwhile to engage a consultant for assistance with.