Sort issue with Group object using IF(MATCH()..) condition

[shyamcharan]

Hi Experts,

I have a group object created with two fields. One field uses IF(MATCH()..) condition to filter out records and the other is normal field.

When I try to sort the chart x-axis(which uses the group object) in Financial Year-Month order,i.e jul Aug Sep Oct .... May June, but it does not work.

I have used an expression to get the values like 201401,201402..201501,201502..201601,201602 etc.

Please find the attached QVW. Can any suggest what I am missing?

[sandeep_sarawgi]

You could change the sort order by simply  using an Inline load such as the one show below and then using FY_Month_Num for sorting in the dimension group you have created.  I do see you don't seem to have data for all months, and therefore, have missing months on X-Axis and that looks odd.  I would suggest using a Master Calendar in addition to something like the Inline load to define your fiscal year sort order.

LOAD * INLINE [
Cal_Month_Num, FY_Month_Num
7, 1
8, 2
9, 3
10, 4
11, 5
12, 6
1, 7
2, 8
3, 9
4, 10
5, 11
6, 12
];


There are many solutions to what you are trying to do.  Please see if this might work to begin with.  Good luck.

[tyagishaila]

Put this

Go to Sort  -->  New group  --> Check Expression

=if(Month_abbreviation='J', 1,

  if(Month_abbreviation='F',2,

  if(Month_abbreviation='M',3,

  if(Month_abbreviation='A',4,

  if(Month_abbreviation='S',5,

  if(Month_abbreviation='O',6,

  if(Month_abbreviation='N',7,

  if(Month_abbreviation='D', 😎

   )

   ))))))

[t_chetirbok]

Hello!

try to make your field with dual

dual(Month_abbreviation,Cal_Month_Num) as Month_abbreviataion

and choose sort for this field

[Gysbert_Wassenaar]

You're using Month_abbreviation as dimension. That fields doesn't have any relation to years. But your Cal_Yr_Mth_Num does. And the are three months that have J as abbreviation and two months with A. That's messing up your chart. Create a normal Month field using the Month() function and use that instead.

[tyagishaila]

Hi Tatsiana,

its good idea ,

but in this case A,J and M has more than 1 Cal_Month_Num Value,

How can resolve this issue of multiple values?

Cheers

[t_chetirbok]

In your data for every Month_abbreviation complies its own Cal_Month_Num

but when you use Month_abbreviation as a dimension A - Apr and A - Aug are the same value

My advice is try to use Cal_Month_Name or Cal_Yr_Mth_Name instead Month_abbreviation

[shyamcharan]

Thanks all for the suggestions.

I have tried the suggestion using the Cal_Month_Name but that did not work. Please find the attached QVW.

It looks like the IF(Match()...) condition is messing up the sort. Without this IF condition it works just fine with any Month field or infact even with Month_abreviation field.

I appreciate all your help and support here.

[sunny_talwar]

May be this as the sort order:

Month(Date#(Cal_Month_Name, 'MMM'))

[t_chetirbok]

Are you sure that you need to use Group in dimension?

You have dimensions: group (Cal_Month_Name and Financial_Year) and Financial_Year, maybe you can use just dimensions =IF(Match(DIM_PriorityMap.Priority_Code,'P1','P2'),Cal_Month_Name) and Financial_Year, without group.

then you can sort your dimension as you want