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

[Ashley Leggore]

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

[Les Johnson 3]

How are you populating your drop-down values

[Ashley Leggore]

WHERE (DAILYVISATRANSACTIONS.DAILYVIS.TRANDATE_MONTH_DEFINE EQ &PROCESS_DATE_MONTH.(FIND DAILYVISATRANSACTIONS.DAILYVIS.TRANDATE_MONTH_DEFINE IN DAILYVISATRANSACTIONS |FORMAT=Mtr).Month.QUOTEDSTRING);

TRANDATE_MONTH_DEFINE is a field from my meta

[Manoj Chaurasia]

Ashley

I would suggest you make a second DEFINE that sets a numeric value of 01 - 12 based on the current month as the starting point and use that as the value from for the drop down parameter setting

[Ashley Leggore]

Thank you! I will try that.

[Martin Yergeau]

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

[Ashley Leggore]

It doesnt seem to like the IF statement in N_ORD.

[Martin Yergeau]

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

[Ashley Leggore]

You are correct, it must be an 8207 thing. I ended up working it the same exact way and broke them down. Thank you!

[Martin Yergeau]

This is a bug that must be reported.

I cannot imagine how many places in code, programmers (such as me) are placing calculation in IF statements to simplify and narrow down the number of code lines

[Toby Mills]

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.

[Martin Yergeau]

toby.mills:

 

I wonder if parenthesis would help get passed that

 

 

Ive tried and the answer is No

 

 

 

toby.mills:

 

Looks to me like the first example should have worked.

 

 

i would have thought too

[Manoj Chaurasia]

This worked for me without error in 8207.27.

[Martin Yergeau]

This is your result Chuck, wheres your code

[David Beagan]

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

[Manoj Chaurasia]

Martin

Sorry I was vague I used the code you posted