Set analysis

[#1]

Hi,

we are displaying 5 years of sales

              2010  2011  2012  2013  2014  2015     Total

Japan    20       40      50     60      70      50         

USA      30      30     40       50       60      80

Requirement:

1)when i select 2015 sales it should show only 2015 sales ant that total only other should display as zero

2)if no selections are there  that should display 5 years sales

thanks

[#2]

PFA Smple file

[swuehl]

As I suggested above, use the intersection operator

sum({<MonthYear *= {'Mar-16'}>} sales)

And maybe Rangesum() for the total.

Or just a YearMonth dimension instead of multiple expressions.

edit:

And I also suggest to use

Date(Monthstart(F3),'MMM-YY') as MonthYear,

to create MonthYear values that reference the monthstart, not distinct dates in the month.

[sunny_talwar]

Again I am not fully sure if we are meeting your requirement or not, but can you check if this works?

UPDATE: just a side note, try using MonthName() function to create MonthYear field or use Date(MonthStart(Date), 'MMM YYYY')

[#5]

Thanks  Almost it meats my requirement.but one thing left.if a user select month it showing respective month ok.

but in my application it contain 5 years data. now i need to show current month and last 5 month of sales only.

like restrict the column like only last five months of sales.should show  but i applied the above solution in my application it showing all   months  data.( i think we need to write different measures in expression tab)

thanks lot.

[jagan]

Hi,

Check this link for rolling 6 months set analysis expression

Set Analysis for Rolling Periods

Regards,

jagan.

[sunny_talwar]

See if the attached is working for you.

[#8]

Hi Sunny, that's a very helpful and a handy way to not only show all dimensions data but also to show the null.

Thanks