Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Did the suggestion provided here did not work? Formatting and alignment of Value based on the expression
Did the suggestion provided here did not work? Formatting and alignment of Value based on the expression
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')
Have you tried Only(VL)?
Yes I tried everything.
What about: Concat(VL, ', ')
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
I would suggest adding VL as one of the dimension
Can I have your contact number sunny ?