Formatting and alignment of Value based on the expression

[#1]

Hi,

I am having a column with the name Value,in the value column i am having different format of data like string date and numbers.

Value Field(Column):

10-03-2016

12-09-2016

ABC

DEF

100

200

ABC-100

DEF-200

I want to show all the values in the pivot using expression.

When i am using SUM(Value) then only getting numbers and when using purgechar then not getting date.Pls help me for expression which will handle all the values accordingly.

[sunny_talwar · Accepted solution]

Did the suggestion provided here did not work? Formatting and alignment of Value based on the expression

[Chanty4u]

hi,

Automatic Number Interpretation

[#4]

Actually the solution is not working I tried many expressions  but records and data is missing

=if(VL=0,0,purgechar(VL,''))
=if(VL=0,0,if(year(VL)>=2015, date#((VL),'YYYYMMDD'),purgeChar(trim(VL),'')))
=keepchar(purgechar(trim(VL),' '),' ')
=if(VL=0,0,if(year(VL)>2015, purgeChar(VL,''),date(Date#(VL,'MM/DD/YYYY'))))
=if(VL=0,0,purgechar(VL,''))
=VL

=if(VL=0,0,if(year(VL)>=2015,Date#(VL,'MM/DD/YYYY'), purgeChar(VL,'')))
=if(VL=0,0,purgeChar(VL,''))
=Date(max({1} MRDate),'MM/DD/YYYY')'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890-'))/=(if([VL] =0,0,purgeChar(VL,' ')))
=PurgeChar(VL,chr(39))/=if(KeepChar(VL,'')=0,0,SubField(vl,'',1))// or (sum(vl))
=if(VL=0,0,KeepChar(VL,' '))
=if(vl=0,0,PurgeChar(PurgeChar([VL],chr(39)),'/+()£$!&*') )

=SUM(VL)
=if(VL=0,0,keepChar(VL,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'))

=purgechar(VL,'abcdefghijklmnopqrstuvwxyz1234567890')

[sunny_talwar]

Have you tried Only(VL)?

[#6]

Yes I tried everything.

[sunny_talwar]

What about: Concat(VL, ', ')

[#8]

Not Working ..

Lets Say I am having 3 values for ABC

ABC 1

ABC 2

ABC 3

DEF 4

DEF 5

DEF 6

I am getting output after using Concat(VL, ', ') as

ABC 1,2,3

DEF 4,5,6

Where expected output is :

ABC 1

ABC 2

ABC 3

DEF 4

DEF 5

DEF 6

and also I am getting date as 42446.00.

Please help ..You can reach me on 9030444224

[sunny_talwar]

I would suggest adding VL as one of the dimension

[#10]

Can I have your contact number sunny ?