Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can some one help me with my month format.
in my Month Filter values are coming like 43252 but i need all my values should be format like 201711.
issue is with few of the KPI's Month values are Coming like 43252 like that so when i Concatenate all my objects in to a Single fact and my month filter is coming as per below snapshot
Try:
If( Len(trim(Month))=5, Date(Month, 'YYYYMM'), Date(Date#(Month,'YYYYMM'),'YYYYMM')) as Month
if(len(Month)=6,date(date#(text(Month),'YYYYMM'),'YYYYMM'), DATE(Month,'YYYYMM'))
Simply you can use
date(Num(Month),'YYYYMM')
First convert the filed into number and then convert that number format into (YYYYMM) as you mentioned.
It depends on how you are getting these values from source. Perhaps try like:
If( Len(trim(Month))=5, Date(Month, 'YYYYMM'), Month) as Month
Below is the snapshot
you can observe now 43040 is changed to 201711 but if see at the bottom another 2017 11 values is there. it will create confusion to users.
Try:
If( Len(trim(Month))=5, Date(Month, 'YYYYMM'), Date(Date#(Month,'YYYYMM'),'YYYYMM')) as Month