i am not clear what you want exactly, but i think you should
use something like this:
(Ceil(MNO,3)*3) instead of pick for the 3, 6, 9 or 12 value.
also for the other values, you can just use Ceil(MNO,3) for quarter numbers.
it is not specific what you want, if this is not what yo want, please specify it more clearly.
Actualy the data I have is in below format
Now I m giving each QTR a number which is like Pick(Match(Upper((QTR)),'JAS','OND','JFM','AMJ'),9,12,3,6)
It check when showing data for any QTR let SAY, JAS it will return the output 9 .
but in the same scenario, for eg JFM , I have assigned the value 3(as per last month of the QTR) , but currently we are sitting in Feb so I want it should return me 2 instead of 3.
Hope I am able to explain .
thanks in advance
so as i understand correctly, your month numbers do not mach with the numbers returning from your table?
why do you not left join an index on months on your QTR table and make a key linking months and qtr?
it will show as following table.
M, QTR, MQTR, NO
J, JAS, J-JAS, 7
A, JAS, A-JAS, 8
S, JAS, S-JAS, 9
Can you please attach an example .qvw file for better understanding?