exclude a specific selection from total

[#1]

Hi,

Need some help for the following:

I have four listboxes namely (MARKET,USUAGE,SPECIES and BRAND) and a bar chart showing 'value share' for different brands for past 1 month.

The expression for the chart is like:

Sum({<DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >} [SALES DOLLARS])

/Sum({<DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >}  TOTAL <MARKET,USAGE,SPECIES> [SALES DOLLARS])

However I don't want the denominator of this expression (which is total) to be effected by the selection in the 'BRAND' listbox i.e. the denominator should be total of what I select in the other three listboxes (MARKET,USUAGE,SPECIES).

Can anyone please tell me how to modify the above expression to ignore the selection in the 'BRAND' listbox?

Thanks

[#2 · Accepted solution]

This should work:

Sum({<BRAND={'*'},DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >} [SALES DOLLARS])

/Sum({<BRAND={'*'},DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >}  TOTAL <MARKET,USAGE,SPECIES> [SALES DOLLARS])

I think this should also work though:

Sum({<BRAND=,DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >} [SALES DOLLARS])

/Sum({<BRAND=,DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >}  TOTAL <MARKET,USAGE,SPECIES> [SALES DOLLARS])

Regards,

Nigel.

[#3]

Thanks but it is not giving the correct numbers. I guess it is ignoring the dimension on the chart also (which is again 'BRAND'). I just want to exclude the selection in the 'BRAND' listbox.

[#4]

Hi

I just built a sample using the expression I suggested and it works fine, I think you're problem might be that you're using Sum( {1.....} Sales) which is designed to disregard all selections, perhaps change this to Sum( {$.....} Sales) and see if that works for you.

If my example is not doing what you want it to do then let me know what you think is wrong, I can then fix the example.

Cheers,

Nigel.

[#5]

Hi Nigel,

My mistake, your earlier expression is working except for a minor change

Sum({<DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >} [SALES DOLLARS])

/Sum({<BRAND={'*'},DATE = {'>=$(=AddMonths(Max(DATE), -1))<=$(=Date(Max(DATE)))'} >}  TOTAL <MARKET,USAGE,SPECIES> [SALES DOLLARS])

Thanks a lot...