Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## Expression count in load script

Hi,

I have an order table and a separate marketing cost table with total cost per country and day.
Now I want to divide the total marketing cost with the number of orders that day.

The tables looks like this:

Orders:

 OrderId OrderDate Country 32432 2019-09-25 US 32433 2019-09-25 US

TotalMarketingCost:

 Date US GB 2019-09-25 50 23

TotalMarketingCost:
Date("Date", 'YYYY-MM-DD') as MarketingDate,
"US" / COUNT( { <Country = 'US',  OrderDate=MarketingDate >}, OrderId)   as CostPerOrderUS, <-- NOT WORKING
//"GB" as MarketingCostGB;

Any help is appreciated!

1 Solution

Accepted Solutions
Partner - Creator II

modified app attached.

best wishes.

8 Replies
Partner - Creator II

Front End = use Set Analysis

Back End Script = use "Group By", "Order By", "Where", etc.

Contributor III
Author

Not sure what to put in the GROUP BY clause. Can you point me in the right direction?

Partner - Creator II

Hi Fredrik,

See app attached.

It is a simple example using Group By and Where clauses.

You can just build it out however you like.

Note:  there is a difference between Sum and Count.

Best wishes.

Contributor III
Author

Thanks for trying to help out, I appreciate it.

I only get the sum of orders for a single country, but not per date. And to also add "date" in group by creates some syntetic key. Any ideas?

Partner - Creator II

modified app attached.

best wishes.

Partner - Master

if you want to calculate values in script (eg count (value) you have to define in group by each field you want to calculate for.

So if you need date, add date as a field and group by date

if you need to calculate by date and country, add both fields and group by date, country.

Regards

Contributor III
Author

Many thanks Wade12 for helping out, that solved my problem!

Partner - Creator II

you're welcome fredrik, best wishes.

Tags
Community Browser