Calculating quarter data as per following month data

Question & Answer
, , , , ,
1

Hi all,
I’m aggregating items as per custom quarter data Q1 = Dec/Jan/Feb by using below custom field:

concat

(

toString(extract('YYYY',addDateTime(1,'MM', truncDate('MM',{created_at (-4h)})))),

' ',

'Q',

toString(extract('Q', addDateTime(1,'MM',truncDate('MM',{created_at (-4h)}))))

)

This working fine with pivot, so I’m able to use this field as a row and calculate received items by those quarters.

image

However I’d like to consider only items which were processed until the end of the following month for each quarter to calculate the completion ratio %. For instance for 2023 Q4 I’d like to count all the items for Q4 and divide them by all the items from 2023 Q4 which were processed until end of December 2023 (first month following Q4).

I’m utilizing date fields like upload_date for received and ship date for completed.

Was able to calculate it by the most recent quarter comparing to now() but would like to calculate it for each quarter in the range.

Looking forward for any hints, thanks.

2

Hi @Rad,

Thanks for your question, I’ve tried to find a solution for this using QuickSight table calculations, but were unable to find a workable solution.

Are you able to pre-aggregate the data before bringing it into QuickSight, calculating the quarterly and quarterly+1 month item counts?

Regards,
Andrew

3

Hi @abacon unfortunately i’m not abble to aggregeate the data because i’m using direct Postgre connection.

4

Hello @Rad, I think you could manage this with an ifelse statement and then LAC-W aggregations.

You could build an ifelse calculated field to group the completions for each Quarter:

ifelse({upload_date} >= '12/01/2022' AND {upload_date} <= '02/28/2023' AND {ship_date} <= '03/31/2023', 'Q1 Completions',
...etc.,
NULL)

Since you cannot create any aggregations in custom SQL, you will need to check for each quarter, and determine if the order was completed. Then you could run a LAC-W function of like distinctCountOver for orders and partition it by the calculated field above to get the total for each quarter.

distinctCountOver({OrderID}, [{Completions Calc}], PRE_AGG)

After this point, it might be tricky with syntax/aggregation errors since we have to run all of these functions in calculated fields. Likely what will need to be done is pulling in the last quarter value utilizing the Lag function and then trying to divide them. If you run into errors at this point, please let me know. But this is likely going to be the way we get this solution.

Alternatively, to avoid Lag and create only distinctCountOver fields, you can write an ifelse statement for each individual quarter, and instead of returning a string field, return the Order ID or whatever field determines an individual order. Then you can run distinctCountOver({Q1 Completions}, [], PRE_AGG) This would allow you to show the total for each quarter on a single row and provide an easier way to aggregate them, it also just requires a lot more calculated fields.