Expression count in load script

[fredrik_olsson]

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:
LOAD
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!

[wade12]

Front End = use Set Analysis

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

[fredrik_olsson]

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

[wade12]

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.

[fredrik_olsson]

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? 

[wade12 · Accepted solution]

modified app attached.

best wishes.

[martinpohl]

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

[fredrik_olsson]

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

[wade12]

you're welcome fredrik, best wishes.