Agrregating 2 data sets by months

Hi all,
I’m currently struggling with 1 data problem. Basically what I’m trying to achieve is to show 2 datasets by 2 different dates within 1 chart. I managed to achieve the expected output by custom SQL query, however it’s very heavy and slows down the servers.The logic behind this chart is following:

x items received in particular > calculated within “Received”
x items completed in particular month > calculated within “Completed”

The tricky part is “Completed”: most of the items are received and completed within the same month but some could be completed within following month as well.

Is there any alternative way using calculated field/function within QS to aggregate this by months separately?

Honestly I tried with several approaches and even I have 2 separate calculated fields for received/completed, how can I join them by Month? In SQL this is solved by recursive function which creates a set of months and counts are joined by month (by date_trunc function).

I’d like to avoid showing this on 2 separate charts which easily applicable.

Expected outcome (by custom SQL calculation):

image

@Rad
QS only supports 1 dataset for 1 visual.
Depending upon which aggregations you are trying to do, you can leverage SPICE for some of the aggregations.

So you have to decide which one is more reasonable in your case.

BTW, If an item was received last month and completed this month; Should it be considered in this month’s completed or last month’s completed ?

1 Like

@neelay thank you for your comments. If an item was received and completed this month it should be counted for this month, within both charts.

1 Like

@Rad “both charts” ? don’t you want to represent this on a single chart?

If possible, it might be better if you can show your sample data, and expected outcome.

1 Like

Sorry i meant both bars within 1 chart. I’ll prepare a sample data for tomorrow to give you more insight on this matter.

@neelay so here’s a sample data i’m utilizing for this exercise.

image

So the expected output in QS would be like:

image

Hi @Rad,
I did’t find solution within QS but mabye this will help speed up SQL calculations.

Try to transform your data using SQL

SELECT 
     itemid
     , 'recived' AS flow
     , recived AS date
FROM my_table_name
UNION 
SELECT 
     itemid
     , 'completed' AS flow
     , completed AS date
FROM my_table_name

and you will get this table
image

And then visualise data (image in another comment)

1 Like

1 Like

Thank you @KarolinaSB will try to adjust SQL to speed up the processing.

1 Like