avg(aggr( distinct count(merchant_id), cust_id, payment_quarter )) using payment_day

[#1]

Hope this is just a simple thing that I am missing.

I have the above expression formula working for me. However I have the source data on the daily basis and the payment_quarter needs to be calculated in the script during the load. I would like to avoid that and do the calculation on the fly.

What would be an equivalent formula if the data is by payment_day and the payment_quarter column is not available?

QuarterName(payment_day) is working just fine for the dynamic dimension. I have tried to use QuarterName(payment_day) or QuarterStart in the formula but become "No data available" error.

Here is the similar question answered, but they have the daily data and need the daily aggregation. I need quarterly aggregation.

count by distinct customer and summed value by date

[Oleg_Troyansky · Accepted solution]

The way you are doing it now is the correct way of doing it. I'd challenge the request to avoid calculating the quarter in the load script.

The more "heavy duty" calculations can be moved from the expressions ("run-time") into the load script (batch load time), there better for the users.

Your specific problem is that the syntax of AGGR requires a Fileld and not a calculated dimension...

The otherway of calculating the same could be:

count(Merchant_ID) / count(distinct cust_id & '-' & QuarterName(payment_day) )

I believe this calculation should produce the same result, only without using AGGR()

[#3]

At the end of the day I have just loaded a calendar table in the form of (date, quarter_id, quarter_name) and used new columns for above formula. Thanks Oleg for your clarification!