Sort Issue with match function

renjithpl — Thu, 17 Oct 2013 15:09:30 GMT

Hi All,

I have an issue in sorting using match function.

I used below code.

=match ( REGION, 'Australia', 'New Zealand', 'Asia', 'Pacific', 'Europe', 'Americas')

It works fine, but my pivot has a MTD Value using set analysis,

my MTD set is:

=Sum({<Day=, Month=, Date={'>=$(=Date(MonthStart(Min(Date))))<=$(=Max(Date))'}>} [Day Actuals])

Problem is, there wont be data available for all countries on few days in a month,

If data available for all countries on a day, the sort works fine,

when not available it does not work, but my pivot gives MTD data but it reflects all the countries in pivot.

Is there any way i nullify Day in sort? or use this expression in sort and has any work around ?

Appreciate your thoughts.

Regards

Ren

Re: Sort Issue with match function

Gysbert_Wassenaar — Thu, 17 Oct 2013 16:09:53 GMT

Perhaps this:

=match (only({<Day=>} REGION), 'Australia', 'New Zealand', 'Asia', 'Pacific', 'Europe', 'Americas')

renjithpl — Thu, 17 Oct 2013 17:01:26 GMT

Works like charm ... Thanks much!!