topic Re: Sort issue with Group object using IF(MATCH()..) condition in QlikView

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

shyamcharan — Wed, 30 Mar 2016 05:53:41 GMT

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?

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

sandeep_sarawgi — Wed, 30 Mar 2016 06:19:38 GMT

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.

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

tyagishaila — Wed, 30 Mar 2016 06:29:44 GMT

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', 😎

)

))))))

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

t_chetirbok — Wed, 30 Mar 2016 06:39:34 GMT

Hello!

try to make your field with dual

dual(Month_abbreviation,Cal_Month_Num) as Month_abbreviataion

and choose sort for this field

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

Gysbert_Wassenaar — Wed, 30 Mar 2016 06:44:29 GMT

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.

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

tyagishaila — Wed, 30 Mar 2016 07:33:51 GMT

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

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

t_chetirbok — Wed, 30 Mar 2016 07:47:45 GMT

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

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

shyamcharan — Wed, 30 Mar 2016 09:29:43 GMT

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.

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

sunny_talwar — Wed, 30 Mar 2016 09:39:13 GMT

May be this as the sort order:

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

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

t_chetirbok — Wed, 30 Mar 2016 09:39:27 GMT

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

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

tyagishaila — Wed, 30 Mar 2016 09:45:46 GMT

It is working fine.

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

shyamcharan — Wed, 30 Mar 2016 09:52:24 GMT

Thanks Sunny. But I need to sort in Financial YEar order.

like Jul, Aug Sept....May June.

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

shyamcharan — Wed, 30 Mar 2016 09:52:57 GMT

Thanks tyagishaila I need a Financial YEar sort instead not the Cal year sort.

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

sunny_talwar — Wed, 30 Mar 2016 09:56:23 GMT

Then try this;

Pick(Month(Date#(Cal_Month_Name, 'MMM')), 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6)

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

shyamcharan — Wed, 30 Mar 2016 09:58:00 GMT

Hi Tatsiana,

Yes i need to group in dimension. I would have Month and Day of Month in the group and not financial year.

Sorry the earlier one was a mistake.

The users want to compare month and day comparison across all three years. Hence I need the group.

I am able to achieve my requirement as in attached. However this is a sample QVW with all fields in a table.

In my actual Data Model I have Priority Code and Month Abbreviation quite apart to each other.

When I move the IF(Match()..) to load script and use the new column, it works fine.

Please find the attached QVW.

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

tyagishaila — Wed, 30 Mar 2016 10:20:04 GMT

I think You have calculated unnecessary fields.

Share your original qvw in which you have available fields and excel file.

Exactly, what output you want?

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

t_chetirbok — Wed, 30 Mar 2016 10:31:06 GMT

you can't use abbreviation as dimension, because Jul, Jun, Jan will be sum as J