Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fredrik_olsson
Contributor III
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:

OrderIdOrderDateCountry
324322019-09-25US
324332019-09-25US

 

TotalMarketingCost:

DateUSGB
2019-09-2550 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!

 

1 Solution

Accepted Solutions
wade12
Partner
Partner

modified app attached.

best wishes.

View solution in original post

8 Replies
wade12
Partner
Partner

Front End = use Set Analysis

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

fredrik_olsson
Contributor III
Contributor III

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

wade12
Partner
Partner

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
Contributor III
Contributor III

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
Partner
Partner

modified app attached.

best wishes.

View solution in original post

martinpohl
Partner
Partner

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
Contributor III
Contributor III

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

wade12
Partner
Partner

you're welcome fredrik, best wishes.