Reference an excel sheet in an expression

[Daniel77]

Hi Experts,

I have a list of Companies i need to exclude in some sales data, I have been adding this to the expression with =- set analysis.

This issue is this is lots of reports and the list keeps being updated.

Is there a way to load the list from a shared drive and then reference this as exclusions of company names?

so currently i have something like this:

Num(
sum({$<
$(v.Date.IgnoreAll),
Year=

{"$(=$(v.CurrentYear))"},
[Month-Year]=

{"<=$(=$(v.LastMonth))"},
Company = {'Company},
[Invoicing Customer name] =-
{
Company names to be excluded here.
}

>}[GBP_Price])

Thank you in advance.

Daniel

[BrunPierre]

Hi, I believe your best bet would be to assign the Invoicing Customer name list to exclude to a variable,

LET vExcludeList = 'Wayne Enterprises, Stark Industries';

And use it within the expression.

[Invoicing Customer name] -={"$(vExcludeList )"}

[Daniel77]

Hey Brun,

Really appreciate the reply, i will give this a go and feedback my experience.

Thanks

Daniel

[Chanty4u]

Num(

sum({$<

$(v.Date.IgnoreAll),

Year={"$(=$(v.CurrentYear))"},

[Month-Year]<={"$(=$(v.LastMonth))"},

Company={'Company},

[Invoicing Customer name]-={$(=concat(distinct CompanyName, ', '))}>

}[GBP_Price])

[Daniel77]

Hi,

Thank you again for sending the suggestion. This has not worked as expected.

I have loaded the vexclude list like this:

LET vExcludeList =

'companyA,companyB,companyC,'

; 

And then in the table i have added to every expression the following in the set analysis in BOLD.

Num(Sum({<
$(v.Date.IgnoreAll),
[Month-Year]={'$(=$(v.LastMonth))'},
[Invoicing Customer name] =-
{"$(vExcludeList )"}
>}GBP_Price),'$(v.Format.Int.0dp)')

When i add invoicing customer name to the table i see that names in the list are still included.

Also,

I found my company names in some cases have ',' in. e.g.

LET vExcludeList =

'companyA,company,B,company,C,'

; 

I tried to single quote the names but this was bad syntax. 

How can i manage the , in the names and also any idea why the names are still in my list?

thank you so much.

Daniel

[Daniel77]

Hi Chanty,

Where do i specify the names to exclude in your suggestion please? i need to set the exclusions in a single list as they appear in hundreds of expressions and i would like to add to the single list to exclude from all expresssions in all tables.

thank you

Daniel

[BrunPierre · Accepted solution]

Try this.

Num(Sum({<
$(v.Date.IgnoreAll),
[Month-Year]={'$(=$(v.LastMonth))'},
[Invoicing Customer name] =-
{$(vExcludeList )}
>}GBP_Price),'$(v.Format.Int.0dp)')