Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
Hi @helena11 ,
Are you looking for the same result for revenue?
no, actually I am looking for a different revenue per country.
Hi @helena11
Please check the below output. hope you are looking the same.
Please check the attached QVF for reference.
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
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);
Hi again @abhijitnalekar ,
thanks for your help. I tried your solution with the mapping tables and it worked.