Blank column Seperator

[nareshthavidishetty]

Hi,

We need to insert an bank Column in the table as below which is marked as red to differentiate the table.

Thanks..

[adamdavi3s]

Just add a blank expression column then.... just put a space as the description and as the expression if you want

example here, we have a grey column:

[miguelbraga]

Hey there,

Just add any expression you want in the between AVGINVOICE and TRAFFIC then add background color and text color RGB(255,255,255) and the label of expression be only a space. Then you have it

Best regards,

D.A. MB

[nareshthavidishetty]

Hi,

We have used single expression to get the o/p..

Logic :

if(KPI= '$(vCurr_WeekDay)' and KPI2='TRAFFIC', num(Sum({<Year={'$(vCurrentYear)'}>}TRAFFIC),'#,##0'),

//'Jul 2016'

if(KPI= vCurr_WeekDay and KPI2= 'CUSTPAYS', Sum({<Year={'$(vCurrentYear)'} >}CUSTPAYS),

if(KPI= vCurr_WeekDay and KPI2='INVCOUNT', Sum({<Year={'$(vCurrentYear)'} >}INVCOUNT),

if(KPI= vLast_WeekDay and KPI2='INVCOUNT', Sum({<Year={'$(vLastYear)'} >}INVCOUNT),

if(KPI= vCurr_WeekDay and KPI2='ADJ TRAFFIC' , num(Sum({<Year={'$(vCurrentYear)'} >}ESTCUST),'#,##0'),

if(KPI= vLast_WeekDay and KPI2='ADJ TRAFFIC', num(Sum({<Year={'$(vLastYear)'} >}ESTCUST),'#,##0'),

//if(KPI='DELTA by MARKET'and KPI2='ADJ TRAFFIC', num(((Sum({<Year={'$(vCurrentYear)'} >}ESTCUST)-Sum({<Year={'$(vLastYear)'} >}ESTCUST))/Sum({<Year={'$(vLastYear)'} >}ESTCUST)),'#.#%'),

//if(KPI='DELTA by SSS'and KPI2='ADJ TRAFFIC', num(((Sum({<Year={'$(vCurrentYear)'} ,Location = p(Location2015)>}ESTCUST)-Sum({<Year={'$(vLastYear)'} >}ESTCUST))/Sum({<Year={'$(vLastYear)'} >}ESTCUST)),'#.#%'),

if(KPI= vCurr_WeekDay and KPI2='CLOSERATE',num( Sum({<Year={'$(vCurrentYear)'} >}INVCOUNT)/Sum({<Year={'$(vCurrentYear)'} >}ESTCUST),'#,##0.0%'),

if(KPI=vLast_WeekDay and KPI2='CLOSERATE', num(Sum({<Year={'$(vLastYear)'} >}INVCOUNT)/Sum({<Year={'$(vLastYear)'} >}ESTCUST),'#,##0.0%'),

if(KPI='DELTA by MARKET'and KPI2='CLOSERATE', num((Sum({<Year={'$(vCurrentYear)'} >}INVCOUNT)/Sum({<Year={'$(vCurrentYear)'} >}ESTCUST))-(Sum({<Year={'$(vLastYear)'} >}INVCOUNT)/Sum({<Year={'$(vLastYear)'} >}ESTCUST)),'#,##0.0%'),

//if(KPI='DELTA by SSS'and KPI2='CLOSERATE',num((Sum({<Year={'$(vCurrentYear)'} >}INVCOUNT)/Sum({<Year={'$(vCurrentYear)'} >}ESTCUST))-(Sum({<Year={'$(vLastYear)'} >}INVCOUNT)/Sum({<Year={'$(vLastYear)'} >}ESTCUST)),'#,##0.0%'),

if(KPI= vCurr_WeekDay and KPI2='WRITTENAMT', num(Sum({<Year={'$(vCurrentYear)'} >}WRITTENAMT),'#,##0.00'),

if(KPI=vLast_WeekDay and KPI2='WRITTENAMT', num(sum({<Year={'$(vLastYear)'} >}WRITTENAMT),'#,##0.00'),

if(KPI= vCurr_WeekDay and KPI2='AVGINVOICE',num( Sum({<Year={'$(vCurrentYear)'} >}WRITTENAMT)/Sum({<Year={'$(vCurrentYear)'} >}INVCOUNT),'#,##0.00'),

if(KPI=vLast_WeekDay and KPI2='AVGINVOICE',num( Sum({<Year={'$(vLastYear)'} >}WRITTENAMT)/Sum({<Year={'$(vLastYear)'} >}INVCOUNT),'#,##0.00'),

if(KPI=vLast_WeekDay and KPI2='TRAFFIC', num(Sum({<Year={'$(vLastYear)'} >}TRAFFIC),'#,##0'),

if(KPI=vLast_WeekDay and KPI2= 'CUSTPAYS', Sum({<Year={'$(vLastYear)'} >}CUSTPAYS),

if(KPI='DELTA by MARKET'and KPI2='TRAFFIC', num(((Sum({<Year={'$(vCurrentYear)'} >}TRAFFIC)-Sum({<Year={'$(vLastYear)'} >}TRAFFIC))/Sum({<Year={'$(vLastYear)'} >}TRAFFIC)),'#.#%'),

//if(KPI='DELTA by SSS' and KPI2='TRAFFIC', num(((Sum({<Year={'$(vCurrentYear)'} ,Location = p(Location2015)>}TRAFFIC)-Sum({<Year={'$(vLastYear)'} >}TRAFFIC))/Sum({<Year={'$(vLastYear)'} >}TRAFFIC)),'#.#%')

))))))))))))))))

Thanks..

[adamdavi3s]

So that formula is pivoted along with a date field above it?

I am not sure you can get a blank column into a pivot in this way

[adamdavi3s]

Maybe add a blank value into the dimension KPI2, this might generate a blank column then if you alter the formula to include it and maybe just put a space in

if(KPI=vLast_WeekDay and KPI2='BLANKSPACE', '' )

[sunny_talwar]

See if this approach is helpful

Column separators in pivot table and double header in straight table

[Anil_Babu_Samineni]

These all are, I mean Variables. Because variables you need to put '$()'

Then, What is the issue you are facing from that above one

[nareshthavidishetty]

Hi Sunny,

I got this..but my table has null values as well which need to be suppress.

After enable Suppress null values option all changes were gone.

Is there any way to suppress null values but the blank columns should remain.

Thanks..

[sunny_talwar]

Here, I changed some stuff around and now you can suppress the null as well