Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
In my script I have following line
If(Match(month(CRE_ACC_PAID_DATE),'May','Jun','Jul'),'MAY-JUN-JUL',If( Match(month(CRE_ACC_PAID_DATE),'Jan','Feb','Mar','Apr','Aug','Sep','Oct','Nov','Dec'),'OTHER-MONTHS',If(Match(month(CRE_ACC_PAID_DATE),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'FULL_YEAR')))AS PERIOD,
The script runs without any issues but in list box for period only two items appear
OTHER-MONTHS |
MAY-JUN-JUL |
Full year is missing. Have I done any mistake above line ? pls help me to correct my to display all 3 items in my list box
OTHER-MONTHS |
MAY-JUN-JUL |
FULL YEAR |
Hi,
Your condition always occur in the first 2 conditions only.. it will not go to the 3rd condition..
Months are 'Jan', 'Feb', ..............'Dec'
so your condition is the same as
If(Match(month(CRE_ACC_PAID_DATE),'May','Jun','Jul'),
'MAY-JUN-JUL',
'OTHER-MONTHS'
) AS PERIOD,
Because your first two conditions are covering all month so your query will never reach to 3rd if statement.
Let us know what exactly you want to do?
It's not quite clear what do you want to do. I suggest that you didn't matched your date with an expression else to connect the date with a master-calendar, see here more: How to use - Master-Calendar and Date-Values. Further helpful to create a kind of additionally asynchron period-fields might be to use The As-Of Table.
- Marcus
Hi,
To associate one month to two period (Jan --> OTHER-MONTH and FULL_YEAR), you need to add a new table (or duplicate your row).
Replace your expression
If(Match(month(CRE_ACC_PAID_DATE),'May','Jun','Jul'),'MAY-JUN-JUL',If( Match(month(CRE_ACC_PAID_DATE),'Jan','Feb','Mar','Apr','Aug','Sep','Oct','Nov','Dec'),'OTHER-MONTHS',If(Match(month(CRE_ACC_PAID_DATE),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),'FULL_YEAR')))AS PERIOD,
by this one :
Month(CRE_ACC_PAID_DATE) AS MONTH_PERIOD,
And add this new table (I use an inline load for the example):
T_PERIOD:
LOAD * INLINE [
MONTH_PERIOD, PERIOD
Jan, OTHER-MONTHS
Feb, OTHER-MONTHS
Mar, OTHER-MONTHS
Apr, OTHER-MONTHS
May, MAY-JUN-JUL
Jun, MAY-JUN-JUL
Jul, MAY-JUN-JUL
Aug, OTHER-MONTHS
Sep, OTHER-MONTHS
Oct, OTHER-MONTHS
Nov, OTHER-MONTHS
Dec, OTHER-MONTHS
Jan, FULL YEAR
Feb, FULL YEAR
Mar, FULL YEAR
Apr, FULL YEAR
May, FULL YEAR
Jun, FULL YEAR
Jul, FULL YEAR
Aug, FULL YEAR
Sep, FULL YEAR
Oct, FULL YEAR
Nov, FULL YEAR
Dec, FULL YEAR
];