Revenue for different date ranges by country in one table

[helena11]

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

[abhijitnalekar]

Hi @helena11 ,

Are you looking for the same result for revenue?

[helena11]

Hi @abhijitnalekar 

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

[abhijitnalekar]

Hi @helena11 

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

Please check the attached QVF for reference. 

[helena11]

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

[abhijitnalekar · Accepted solution]

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);

[helena11]

Hi again @abhijitnalekar ,

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