Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a data structure in which my products are grouped in franchises, and franchises grouped per country.
The country filter includes a "sum of all countries"-variable called "Nordic".
When a franchise is selected, I still want to show the actuals for the other franchises (just as with the monthly target):
I have used the following formula:
=If(Country='Nordic',sum({<[Country]=,[Franchise]=>} Sales),sum({<Franchise=>} Sales)), but all I see is the total, which is correct.
Can you help me figuring out how to get the actuals per franchise when country=Nordic?
Thanks in advance!
BR
Novarbis
Need an inline statement with sample data in to help and a copy of your load script.
Hi Andrew,
Thanks for getting back to me regarding my problem.
What is an "inline statement"?
Thanks!
BR
Tobias
A way of loading data manually in the load script:
Tmp:
Load * Inline [
Year, count. // these are the column headers//
2019, 49. //this is the data//
2020, 67
2021, 89
];
Hi,
Thanks for getting back to me. I tried to recreate the data below.
The data is structured in a way so each Country contains the same franchises, and each franchise contains a range of products, with sales and targets.
The countries are 'SE' , 'DK' and 'Nordic' where 'Nordic' is a group of 'SE' and 'DK'.
The franchises are 'IHD' and 'CRM'.
The products in IHD are 'ABC' and 'DEF'
The products in CRM are '123' and '456'.
In addition, there are no sales at Nordic level. When Nordic is selected, the sum of the sales in DK and SE is shown.
Load * Inline [
Franchise, Product, Country, Sales, Target
IHD, ABC, Nordic, , 800
IHD, DEF, Nordic, , 400
CRM, 123, Nordic, , 500
IHD, ABC, DK, 20, 300
IHD, ABC, SE, 200, 500
IHD, DEF, DK, 25, 100
IHD, DEF, SE, 50, 300
CRM, 123, SE, 120, 250
CRM, 123, DK, 300, 250
];
When Nordic is selected, I want to show sales and targets for all franchises even when one franchise is selected, and I tried with below formula for sales, but without any luck. When I select either DK or SE it works.
So it is the combination of showing the sum of all countries and all franchises which does not work.
If(Country='Nordic',sum({<[Country]=,[Franchise]=>} Sales),sum({<Franchise=>} Sales))
I have attached the app here, which illustrates the problem.
Thanks in advance!
BR
Tobias