topic Re: Blank column Seperator in QlikView

Blank column Seperator

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

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

Thanks..

Re: Blank column Seperator

adamdavi3s — Wed, 28 Sep 2016 10:40:35 GMT

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:

Re: Blank column Seperator

miguelbraga — Wed, 28 Sep 2016 10:46:13 GMT

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

Re: Blank column Seperator

nareshthavidishetty — Wed, 28 Sep 2016 10:48:57 GMT

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

Re: Blank column Seperator

adamdavi3s — Wed, 28 Sep 2016 10:52:33 GMT

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

Re: Blank column Seperator

adamdavi3s — Wed, 28 Sep 2016 10:54:30 GMT

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', '' )

Re: Blank column Seperator

sunny_talwar — Wed, 28 Sep 2016 10:57:22 GMT

See if this approach is helpful

Column separators in pivot table and double header in straight table

Re: Blank column Seperator

Anil_Babu_Samineni — Wed, 28 Sep 2016 10:58:31 GMT

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

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

Re: Blank column Seperator

nareshthavidishetty — Wed, 28 Sep 2016 11:55:08 GMT

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

Re: Blank column Seperator

sunny_talwar — Wed, 28 Sep 2016 12:08:50 GMT

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

Re: Blank column Seperator

nareshthavidishetty — Wed, 28 Sep 2016 12:14:54 GMT

Hi Sunny,

May i know what you done to get this.Please let me know can't figure out.

Thanks..

Re: Blank column Seperator

sunny_talwar — Wed, 28 Sep 2016 12:19:16 GMT

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?

Re: Blank column Seperator

nareshthavidishetty — Thu, 29 Sep 2016 03:59:44 GMT

Thanks...Sunny