Selected Date Set Analysis that will allow other selections to be made

[#1]

Hi all,

I have a problem in my understanding of how to best implement Set Analysis.

I have a number of fields; AccountName, AccountNumber, Product, Country, DollarAmount, InvoiceYear, InvoiceMonth

I have so far created a graph that allows me to display a 12 month barchart going back over the current selected year on a month by month basis based on a selection by using the following expression:

Sum ({1<InvoiceYear={$(=Only(InvoiceYear))}>} DollarAmount)

This works well, however when I drill into a selection (say Country) the graph remains the same (as I assume that it is because the Set Analysis Expression is looking at the complete dataset and is ignoring any selections.

What I would like is for the user to be able to select a date (say Jan 09) and the graph show the entire 12 month breakdown for the chosen year (2009), yet still allow further selections to be made (such as product) and the graph updated to show the previous 12 months for say Australia.

Any ideas,

Thanks,

Michael

[#2]

Hi Michael,

Try this expression above, I took off the (1) and i put the Total, the total allow the values to goes to every fields of the dimension and the 1 allow get every values on the cloud.

Sum ({<InvoiceYear={$(=Only(InvoiceYear))}>} TOTAL DollarAmount)

[#3]

Hi Júnior,

That does not work, it now shows all 12 months as exactly the same value.

On a plus note, the graph does appear to chage when further selections are made.

[#4 · Accepted solution]

You say you're making the selection on a Month-Year field. You want the Year and every other field selection to be valid, except that month.

Try something like:

Sum ({<MonthYear = {*}, InvoiceYear={$(=Only(InvoiceYear))}>} DollarAmount)

That will basically ignore the MonthYear field selection. It will only Sum for the invoice year relative to that selection. And any selection made in any other field will be respected.

[#5]

Hi,

you have as Dimension just Month . you must have month-Year as Dimension.

[#6]

Thank you, it works perfectly!