Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

Blank column Seperator

Hi,

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

Untitled.png

Thanks..

1 Solution

Accepted Solutions
sunny_talwar

I have attached the QlikView document with my response. But essentially, this was the old expression

If(SNo2 = 1,

Num(Pick(Match(SNo1, 1, 3, 5, 7),

  [Sales MTH],

  [Sales RQTR],

  [Sales YTD],

  [Sales MAT]), '#.##0'),

Num(Pick(Match(SNo1, 1, 3, 5, 7),

  [Growth MTH],

  [Growth RQTR],

  [Growth YTD],

  [Growth MAT]), '##.0%'))

and this is the new one:

If(SNo2 = 1,

Pick(SNo1,

  Num([Sales MTH], '#.##0'),

  Dual(' ', 1),

  Num([Sales RQTR], '#.##0'),

  Dual(' ', 1),

  Num([Sales YTD], '#.##0'),

  Dual(' ', 1),

  Num([Sales MAT], '#.##0')),

Pick(SNo1,

  Num([Growth MTH], '##.0%'),

Dual(' ', 1),

  Num([Growth RQTR], '##.0%'),

Dual(' ', 1),

  Num([Growth YTD], '##.0%'),

  Dual(' ', 1),

  Num([Growth MAT], '##.0%')),)

So, all I did was to assign a value of 1 to the column which was getting suppressed because it was null before. Does this make sense?

View solution in original post

12 Replies
adamdavi3s
Master
Master

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:

Capture.PNG

miguelbraga
Specialist III
Specialist III

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
Creator III
Creator III

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
Master
Master

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
Master
Master

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nareshthavidishetty
Creator III
Creator III

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

Capture.PNG