Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please Follow the below scenario:
Period
Period | Converted |
2016001 | Apr |
2016002 | May |
2016001 | Jun |
2016003 | July |
2016005 | Aug |
2016006 | Sept |
2016007 | Oct |
2016008 | Nov |
2016009 | Dec |
2016010 | Jan |
2016011 | Feb |
2016012 | March |
2016013 | March |
2016014 | March |
2016015 | March |
There is period column. User Wants that 01 from 2016001 should be converted in Apr and so on but last number as mentioned above that are 13, 14, 15 should be converted in March only.
And on that basis wants the calendar should be started from April only.
I used below mentioned script but sales not coming correct . Please Help me
vw_vil_acctrans:
LOAD
Period,
if(right(Period,2)>=12,12,right(Period,2)) as Periods,
Sales
FROM [lib://Data/MakeDate.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
vw_vil_acctrans1:
Load Sales,Date(MakeDate(left(Period,4),Periods),'DD/MM/YYYY') as MD
Resident vw_vil_acctrans;
Please help me.
I think you should connect your period-field with a normal master-calendar and a fiscal-calendar and/or using an As-Of-Table. Here you will find many informations and examples about: How to use - Master-Calendar and Date-Values.
- Marcus