Month Format

[dunnalahk123]

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 

[pradosh_thakur]

if(len(Month)=6,date(date#(text(Month),'YYYYMM'),'YYYYMM'), DATE(Month,'YYYYMM'))

[surendraj]

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.

[tresesco]

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

[dunnalahk123]

HI,
It is Coming , but coming as separate line

like 201711 and now changed value 43252 also coming as 201711.

but normally in filters it should show only once right.

[dunnalahk123]

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.

[dunnalahk123]

[tresesco · Accepted solution]

Try:

If( Len(trim(Month))=5, Date(Month, 'YYYYMM'), Date(Date#(Month,'YYYYMM'),'YYYYMM')) as Month

[dunnalahk123]

No, Still i see 2 lines with same values.

[dunnalahk123]

IT Works.