Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

I have created a report that shows a line chart and compares...


Ashley Leggore

Recommended Posts

I have created a report that shows a line chart and compares one month to the same month for the previous year. Everything is working except my drop down parameter is in alphabetical order. I need this to be in month order with most recent month on top. This is how I want it to look:

June

May

April

March

February

January

December

November

October

September

August

July

Then next month, July would be on top and so on. Im having difficulty getting this to work correctly and I believe it has to do with the fact that Im not displaying the month and the year. Does anyone have any suggestions

Link to comment
Share on other sites

Maybe not the most elegant way, but it works

DEFINE FILE GGSALES

ACTMTH /MONTH = &YYMD;

MTH /MONTH = DATE;

MTHR /I2 = DECODE MTH (1 12, 2 11, 3 10, 4 9, 5 8, 6 7, 7 6, 8 5, 9 4, 10 3, 11 2, 12 1);

MTHX /Mtr = DATE;

N_ORD /I2 = IF MTHR GT ACTMTH THEN MTHR - ACTMTH + 1 ELSE MTHR + ACTMTH + 1;

END

TABLE FILE GGSALES

BY N_ORD NOPRINT

BY MTHX AS 'Month'

-*BY MTH

-*BY MTHR

-*BY ACTMTH

END

-RUN

Link to comment
Share on other sites

My sample is working well using 8204

Seems that using 8207 performing a calculation in a IF statement its not acceptable

It does work with the below

DEFINE FILE GGSALES

ACTMTH /MONTH = &YYMD;

MTH /MONTH = DATE;

MTHR /I2 = DECODE MTH (1 12, 2 11, 3 10, 4 9, 5 8, 6 7, 7 6, 8 5, 9 4, 10 3, 11 2, 12 1);

MTHX /Mtr = DATE;

ORD1 /I2 = MTHR - ACTMTH + 1;

ORD2 /I2 = MTHR + ACTMTH + 1;

N_ORD /I2 = IF MTHR GT ACTMTH THEN ORD1 ELSE ORD2;

END

TABLE FILE GGSALES

BY N_ORD NOPRINT

BY MTHX AS 'Month'

-*BY MTH

-*BY MTHR

-*BY ACTMTH

END

-RUN

Link to comment
Share on other sites

I concur - this is what IBI calls a UC (upward compatibility).

What message comes back on this (wonder if we can tell what WF was thinking)

I wonder if parenthesis would help get passed that

From the crlang manual for 8207.27

 

image.png857210 20.4 KB

 

Looks to me like the first example should have worked.

Link to comment
Share on other sites

I tried MartinYs original code on 8.2.06 and received the error:

(FOC266) IF THEN ELSE SYNTAX ERROR

I could make the error go away with one change:

DEFINE FILE GGSALES

-* ACTMTH /MONTH = &YYMD;

ACTMTH /I2 = &DATEM;

MTH /MONTH = DATE;

MTHR /I2 = DECODE MTH (1 12, 2 11, 3 10, 4 9, 5 8, 6 7, 7 6, 8 5, 9 4, 10 3, 11 2, 12 1);

MTHX /Mtr = DATE;

N_ORD /I2 = IF MTHR GT ACTMTH THEN MTHR - ACTMTH + 1 ELSE MTHR + ACTMTH + 1;

END

TABLE FILE GGSALES

BY N_ORD NOPRINT

BY MTHX AS 'Month'

END

 

It seems that it isnt simply a calculation in an IF THEN ELSE that is the problem, but sometimes when you do it with a MONTH data type. I say sometimes, because the following works in 8.2.06 just by changing the order of the fields in the THEN calculation:

DEFINE FILE GGSALES

ACTMTH /MONTH = &YYMD;

MTH /MONTH = DATE;

MTHR /I2 = DECODE MTH (1 12, 2 11, 3 10, 4 9, 5 8, 6 7, 7 6, 8 5, 9 4, 10 3, 11 2, 12 1);

MTHX /Mtr = DATE;

N_ORD /I2 = IF MTHR GT ACTMTH THEN - ACTMTH + 1 + MTHR ELSE MTHR + ACTMTH + 1;

END

TABLE FILE GGSALES

BY N_ORD NOPRINT

BY MTHX AS 'Month'

END

 

All that being said, Im not sure the code works for months other than June. For example, if I put

SET TESTDATE=20210701

-RUN

 

At the beginning of the code the first one on the list is May, but should be July.

Here is what I would suggest:

DEFINE FILE GGSALES

Month/Mtr = DATE;

Order/A2 = DIGITS(MOD(&DATEM - Month + 12, 12) + 1, 2);

END

TABLE FILE GGSALES

SUM Month

BY Order

END

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...