Can one expression reference multiple states?

[#1]

I've recently created my first app using multiple states, including a line chart in which each of its 2 expressions pull from a different state. The 2 expressions that I've built are simply:

- Avg([SALES])

- Avg({[Benchmark]} [NET SALES])

The result is the chart below, which compares the average weekly sales of whatever I've select as the "Focus" product vs. the average weekly sales of whatever I've selected as the group of benchmark products. My issue is that I only want to see the Benchmarks' sales for the same number of weeks that the Focus product was in market. The below chart is a perfect example: the Focus product was only in market for 15 weeks, so I only want to see the first 15 weeks of the Benchmarks' sales as well (and I want this to dynamically change depending on the product that I've selected as my Focus). Is it possible to write an expression that accomplishes this - setting the Selling Weeks based on the weeks where the Focus product has positive sales, but setting everything else based on the alternate state?

Thanks!

Jeff

[MarcoWedel]

yes, using set expressions.

[#3]

Thanks Marco. Would you be able to write out an example of the kind of expression you would write for this? And I clarified what I'm looking for a bit above: an expression that sets the Selling Weeks based on the weeks where the Focus product has positive sales, but sets everything else based on the alternate state.

[mark_casselman]

Hi Jeff,

You can find some amazing examples in this document Set Analysis: syntaxes, examples .

On top of page 17 you will find an example that should solve your question I think.

Can you try:

sum({[Benchmark] <[SELLING WEEK] = [Focus] :: [SELLING WEEK]> } Sales)

It should only sum up the sales for the weeks that also appear in the Focus state.

[#5]

Thanks Mark - great resource and that does get me closer to my goal! This at least lets me set the Selling Weeks based on the weeks I've selected in the Focus state. Ideally though, I want the chart to automatically use just the weeks where the Focus product has sales > 0. Do you know how the expression would need to be modified to do this?

[mark_casselman]

Just a quick guess based on page 12 of the same document:

sum({[Benchmark] <[SELLING WEEK] = {"=sum([Focus] :: [SELLING WEEK])>0"} > } Sales )

But wouldn't it be confusing to the user that some weeks could be missing on the x-axis ?

[#7 · Accepted solution]

Aha - that expression didn't quite work, but it gave me an idea for another idea that did! A simple if statement got the job done. Here's the final expression that I used:

if(Avg({[Focus]} [SALES])>0, Avg({[Benchmark]} [SALES]), 0 )

Thanks for the help Mark!