Multiple set expressions and sum formula

[Novarbis]

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

[andrew_smith200]

Need an inline statement with sample data in to help and a copy of your load script.

[Novarbis]

Hi Andrew,

Thanks for getting back to me regarding my problem.

What is an "inline statement"?

Thanks! 

BR
Tobias

[andrew_smith200]

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

];

[Novarbis]

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

[Novarbis]

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.