Timeseries: % split of dimension

[#1]

Hi there,

I have a time series chart that looks at % of total sales for each salesman for each period. My dimensions are Opsdate and Salesman and my expression is:

Sum(Sales) / Sum (TOTAL <Opsdate> Sales)

My user wants me to adjust the chart so when he selects the a Salesman the chart just drops the other salesman and the selected Salesman % stays the same. With my existing formula it just shows a flat line at 100% because both the numerator and the denominator are adjusted by the selections. I want the denominator not to be impacted by the Salesman selection. So I tried the following formula:

Sum(Sales) / Sum ( {<Salesman>} TOTAL <Opsdate> Sales

But it doesn't seem to work. I played about with Aggr functions but have had no joy.

Thanks in advance

Max

[alexandros17]

try this for denominator:

Sum (1 {<Salesman>} TOTAL Sales

[joshabbott]

Looks like you are missing an ending parenthesis and an equal sign:

sum(Sales) / sum({<Salesman=>} TOTAL <Opsdate> Sales)

[#4]

Thanks

This is good but it forces it to be the total of all the data regardless of other selections made.

Ideally what I am looking for is if I select another filter, for example Region = Europe, then the chart shows the % of Sales for each Salesman for that Region. It is only the Salesman dimension that I want to be ignored in the denominator.

[joshabbott]

The <Salesman> should be <Salesman=> to ignore the selection in the set modifier portion of the set analysis

[alexandros17]

You're right, so try:

Sum(Sales) / Sum (TOTAL <Salesman > Sales)

let me know

[#7]

You can actually use either, both give the same result of ignoring the Salesman selection

[#8]

Thanks Josh, I would have thought that formula should work but it the Salesman bit doesn't seem to do anything, when I select a single Salesman it still shows as 100%. Could it be because Salesman is one of my dimensions in the chart?

[#9]

Thanks for your help. We are getting closer but this formula ignores the date so the chart shows each weeks sales for each salesman divided by total sales over all periods?

[joshabbott]

I really don't think so.  Is there another expression in your chart?  Is the 'Salesman' the exact same dimenstion that is in your dimension, I think if this is cased wrong or spelled different, it may do this.