topic Revenue for different date ranges by country in one table in App Development

Revenue for different date ranges by country in one table

helena11 — Mon, 18 Oct 2021 09:27:33 GMT

Dear community,

I am struggling with a set expression to display the Sum(Revenue) achieved by each country within a promotion time frame. The problem is, that the products included in the promotion and the time frame for the promotion differ across countries although its the same promotion.

This is a simplified version of my data model:

And this is what the final table should contain:

Now I do get this to work if the user selects only one country from the filter pane and in consequence only one country is displayed in the table. The formula for that looks like this:

Sum({<[Order Date]={">=$(=Only([Promotion Start]))<=$(=Only([Promotion End]))"}>} If(Wildmatch([Country Name],GetFieldSelections([Country]),[Revenue]))

But as soon as I want to have one table that contains all countries I run into problems as how to account for the different date ranges and product ranges for each country. Does anyone have a suggestion how I could solve this?

Thanks in advance

Re: Revenue for different date ranges by country in one table

abhijitnalekar — Mon, 18 Oct 2021 15:06:27 GMT

Hi @helena11 ,

Are you looking for the same result for revenue?

Re: Revenue for different date ranges by country in one table

helena11 — Tue, 19 Oct 2021 06:21:17 GMT

Hi @abhijitnalekar

no, actually I am looking for a different revenue per country.

Re: Revenue for different date ranges by country in one table

abhijitnalekar — Tue, 19 Oct 2021 06:43:48 GMT

Hi @helena11

Please check the below output. hope you are looking the same.

Please check the attached QVF for reference.

Re: Revenue for different date ranges by country in one table

helena11 — Tue, 19 Oct 2021 11:44:22 GMT

Hi @abhijitnalekar

if you solved it with a set expression can you maybe post it here? My company does not allow the import of external QVF files.

Thanks

Re: Revenue for different date ranges by country in one table

abhijitnalekar — Tue, 19 Oct 2021 11:51:59 GMT

Hi @helena11 ,

Please check the below script.

Mcountry:
mapping
LOAD
OrderCountryID,
CountryName
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is [Country Detail]);

Moroder:
Mapping
LOAD
OrderID,
ProductID
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is [Order Detail]);

Tab1:

LOAD
"Order ID" as OrderID,
OrderCountryID,
ApplyMap('Mcountry',OrderCountryID) as CName,
Applymap('Moroder',"Order ID") as ProductID,
"Order Date",
Revenue
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is [Order Review]);

LOAD

Country as CName,
"Promotion Start",
"Promotion End"
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is PramotionDetail);

Re: Revenue for different date ranges by country in one table

helena11 — Tue, 26 Oct 2021 08:15:41 GMT

Hi again @abhijitnalekar ,

thanks for your help. I tried your solution with the mapping tables and it worked.