Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to have a field which shows the year then month of the transaction as a field selected in my reports. I've highlighted my attempt to create the field. Can someone please advise me what I have done wrong?
LOAD
`st_account` as %Key.Customer,
`st_account`,
`st_trdate`,
date(date#(`st_trdate`, 'dd-mm-yyyy'),'yyyy-mm') as Period,
`st_trref` AS %Key.Trans,
`st_custref`,
`st_trtype`,
`st_trvalue`,
`st_vatval`,
`st_trbal`,
`st_paid`,
`st_crdate`,
`st_advance`,
`st_payflag`,
`st_dueday`,
`st_type`,
`st_delacc`,
id,
`st_pedate`;
SQL SELECT `st_account`,
`st_trdate`,
`st_trref`,
`st_custref`,
`st_trtype`,
`st_trvalue`,
`st_vatval`,
`st_trbal`,
`st_paid`,
`st_crdate`,
`st_advance`,
`st_payflag`,
`st_dueday`,
`st_type`,
`st_delacc`,
id,
`st_pedate`
FROM stran;
May be like this,
Date(Floor(st_trdate),'YYYY-MM')
What is the format for `st_trdate' field coming from your Database?
guess
Date(st_trdate,'YYYY-MM') as Period
should be fine.
What is the error you are getting?
Thank you!
May be using Preceding Load. Try with these 3 conditions
Load *, date(date#(`st_trdate`, 'dd-mm-yyyy'),'yyyy-mm') as Period;
//Load *, date(date#([`st_trdate`], 'dd-mm-yyyy'),'yyyy-mm') as Period;
//Load *, date(date#(st_trdate, 'dd-mm-yyyy'),'yyyy-mm') as Period;
LOAD
`st_account` as %Key.Customer,
`st_account`,
`st_trdate`,
`st_trref` AS %Key.Trans,
`st_custref`,
`st_trtype`,
`st_trvalue`,
`st_vatval`,
`st_trbal`,
`st_paid`,
`st_crdate`,
`st_advance`,
`st_payflag`,
`st_dueday`,
`st_type`,
`st_delacc`,
id,
`st_pedate`;
SQL SELECT `st_account`,
`st_trdate`,
`st_trref`,
`st_custref`,
`st_trtype`,
`st_trvalue`,
`st_vatval`,
`st_trbal`,
`st_paid`,
`st_crdate`,
`st_advance`,
`st_payflag`,
`st_dueday`,
`st_type`,
`st_delacc`,
id,
`st_pedate`
FROM stran;
Try
date(Floor(st_trdate),'yyyy-mm') as Period
hth
Sas
Your problem is that you will get mutiple values of each period, with the underlying value being the actual date (ie up to 31 distinct values). The Date() function does not truncate the day when you format with something like 'yyyy-mm', it just does not display it.
You need
Date(MonthStart(Date#([st_trdate], 'dd-mm-yyyy'),'yyyy-mm')) as Period,
Date(MonthStart(Date#([st_trdate], 'dd-mm-yyyy')), 'yyyy-mm') as Period,
This ensures that there is only one possible value for each Period.
(edit - bracket placement corrected)
Hi,
What kind of issue you are facing???
in date format
mm - > minutes
MM -> Month
Check date format also.
Regards,
Prashant
May be like this,
Date(Floor(st_trdate),'YYYY-MM')
try this Expression,
=Date(floor(st_trdate),'YYYY-MM')
Thank you for your help this has resolved the problem.