Set-Analysis, ignore dimension

[#1]

Hi,

I am trying to calculate YTD using set analsyis. I have three dimesions: Year, Quarter and Month. I am using this expression in my object:

sum({$<MonthID = {"<=$(=Max(MonthID))"},

Year = {$(=Max(Year))},

Quarter = ,

Month = >} Sales)

As I have understood the syntax for Set Analysis, the statement "Quarter=" should ignore any selections in that dimension. I.e. If I choose Year = 2011, Quarter = Q2, Month = September, my table should show the value for 2011 September, not the value for 2011 Quarter Q2. But whenever I choose anything in the dimension Quarter, my value in the column YTD changes. Can you help me with this?

Thanks for your help!

Best Regards

Helena

[swuehl]

Hi Helena,

I guess when you select a different quarter, like selecting Q1, your current selection of MonthID resp. max(MonthID) is also changing, isn't it (unless you only add quarters with correlated monthID <= current max MonthID)?

Not sure if I understood your problem correctly, though. Could you upload a sample here?

Regards,

Stefan

[avastani]

why not use the InYearToDate() function?

[#4]

Hi Stefan.

Thanks for your answer. Yes, the current selection of MonthID changes if I select a different quarter. But I thought that if I used the set analysis like this:

sum({$<MonthID = {"<=$(=Max(MonthID))"},

Year = {$(=Max(Year))},

Quarter = ,

Month = >} Sales)

the value should not change if I selected anything in the Quarter listbox. I have attached an example of my Set Analysis.

What I would like to do is that the value in the column YTD should only be changed if I choose anything in the Year and Month listboxes. If the user choose anything in the Quarter listbox, the column YTD should ignore that selection.

Do you know if this is possible to do?

Best Regards

Helena

[#5]

try using

Sum({$<[Month Num] = {"<=$(=Max({<[Quarter] =>}[Month Num]))"},[Year] = {$(=Max(Year))}, [Quarter] =>} Antal)

Regards,

GM

[Pulkit_Thukral]

Please confirm in calculating YTD ,why are you choosing max month ?

>> If in case  you want a static YTD ignoring month and quarter,then below expression you can try :

sum( {$<Year={"$(=DATE(FLOOR(YearStart(Today())),'YYYY'))"},Month=,Quarter=>} Sales)

>> or else if you want year to be changed along with month selection also ,then try this :

sum( {$<Year={"$(=DATE(FLOOR(YearStart(max(Month))),'YYYY'))"},Quarter=>} Sales)