Expand my Community achievements bar.

SOLVED

How to get the average daily sent per delivery code in a month?

Avatar

Level 3

In my query in the additionnal column, in my ouput column I am trying to edit an expression to  get the  average of the daily success avg = ((success sent) x last 30 days)/30 per delivery code

But I am not sure to well write the function to use, any tips to wrote that down properly?

 

I have tried something like:

@averageDailySuccess = (Sum(IIF(@operation='D' AND @logDate > GetDate()-30, @successCount, 0)))/30

 

but It give an overall average over the last 30 days and not the average per delivery code. To get the average per delivery code, you would usually need to group by the delivery code and calculate the average for each group. This might not be possible within a single additional column expression - it may require additional steps or adjustments in how your data is queried or processed. But I am not sure about how to do this too

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @rbiri ,

You can try the below query configuration,

ParthaSarathy_0-1686639554833.png

 

Targeting Dimension: nms:delivery

Filtering Dimension: nms:delivery

Query:

 @created on or after DaysAgo(30)

ParthaSarathy_1-1686639652960.png

In my case, in preview tab,

ParthaSarathy_2-1686639756518.png

 

So the expected average of deliveryCode2 is 2 and deliveryCode3 is 3 and deliveryCode4 is 4.

 

Complementary information > add data > 

- select delivery code (@deliveryCode)

- Add an expression >      Avg([indicators/@success])

Click Group check box on @deliveryCode alone > finish > save the query

ParthaSarathy_3-1686639866617.png

 

Reopen the query > edit additional data > advanced parameter > do the configuration as below,

ParthaSarathy_4-1686640000797.png

And run the workflow,

The output:

ParthaSarathy_5-1686640040613.png

Received the expected average success per delivery code on last 30 days.

 

 

 

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

Hi @rbiri ,

You can try the below query configuration,

ParthaSarathy_0-1686639554833.png

 

Targeting Dimension: nms:delivery

Filtering Dimension: nms:delivery

Query:

 @created on or after DaysAgo(30)

ParthaSarathy_1-1686639652960.png

In my case, in preview tab,

ParthaSarathy_2-1686639756518.png

 

So the expected average of deliveryCode2 is 2 and deliveryCode3 is 3 and deliveryCode4 is 4.

 

Complementary information > add data > 

- select delivery code (@deliveryCode)

- Add an expression >      Avg([indicators/@success])

Click Group check box on @deliveryCode alone > finish > save the query

ParthaSarathy_3-1686639866617.png

 

Reopen the query > edit additional data > advanced parameter > do the configuration as below,

ParthaSarathy_4-1686640000797.png

And run the workflow,

The output:

ParthaSarathy_5-1686640040613.png

Received the expected average success per delivery code on last 30 days.