topic Re: pivot table help in QlikView

pivot table help

Mon, 14 Jul 2014 08:18:15 GMT

I am trying to achieve the pivot tablet to be calucalted properly but I could not

kindly help me out in exact solution.

Re: pivot table help

marcus_sommer — Mon, 14 Jul 2014 09:34:43 GMT

Try this: column(4) / column(1)

- Marcus

Re: pivot table help

Mon, 14 Jul 2014 09:37:42 GMT

I used but not giving desired result

can you brief me a bit more how to and where to use it

Re: Re: pivot table help

marcus_sommer — Mon, 14 Jul 2014 10:11:08 GMT

You need further if-conditions in your expressions - with only one expressions you couldn't use references to other columns. Here simplified without your condition on south pacific:

=IF(Match(ServiceOLR.Type,'Budget','Actuals'), NUM(SUM(ServiceOLR.Amount), '#,##0'), IF(Match(ServiceOLR.Type,'Calc'),

NUM(

SUM({1< ServiceOLR.Type = {'Actuals'} >}

total <ServiceOLR.Sub_Region, ServiceOLR.Country, ServiceOLR.PL> ServiceOLR.Amount) /

SUM({1< ServiceOLR.Type = {'Budget'} >}

total <ServiceOLR.Sub_Region, ServiceOLR.Country, ServiceOLR.PL> ServiceOLR.Amount), '#,##0%')))

- Marcus

Re: pivot table help

karolina_ — Mon, 14 Jul 2014 10:12:51 GMT

you use column(#) for expressions if you have few/severals, instead of column(#) you can use [label_name]
but not in your case as you have only one expression - so one column
you need to do calculation inside expression

Re: pivot table help

alexandros17 — Mon, 14 Jul 2014 10:14:04 GMT

I sent you my example, let me know

Re: pivot table help

Tue, 15 Jul 2014 07:53:57 GMT

Thanks Marcus for guiding me though

Re: pivot table help

Wed, 16 Jul 2014 12:33:08 GMT

Marcus Soomer,

Thanks for providing me the solution.

here i need some more help from you

how to remove extra spaces after that "country dimension" in the webview.

Do suggest me

Re: pivot table help

marcus_sommer — Wed, 16 Jul 2014 12:54:58 GMT

A column-width of 0 isn't possible within the gui but minimizing could be done without enabling compact view and the you could it disable again:

- Marcus

Re: pivot table help

Wed, 16 Jul 2014 12:59:48 GMT

Hi Marcus

Thanks for responding me

i am not getting the exact solution what i was trying for spaces are still existing after the country dimension

Re: pivot table help

marcus_sommer — Wed, 16 Jul 2014 13:08:09 GMT

More isn't possible ...

- Marcus

Re: pivot table help

Wed, 16 Jul 2014 13:14:39 GMT

Marcus

Thanks for your efforts

same i am applying at my end but i coulnot get the result as u get.

Re: pivot table help

marcus_sommer — Wed, 16 Jul 2014 13:21:34 GMT

I meant disable the option, drag the column-width and enable the option again. If it don't work change web-view to normal-view, drag the column-width and change to webview.

- Marcus

Re: pivot table help

Wed, 16 Jul 2014 13:53:46 GMT

Thanks Marcus

i really excited with your aproach

really impressed with you Marcus

Thanks again

one more small confusion how to rename the calc column in our pivot table?

Re: pivot table help

marcus_sommer — Wed, 16 Jul 2014 14:06:23 GMT

You meant for the dimension Type the value calc? Then: if(Type = 'Calc', 'RenameValue', Calc) as calculated dimension or directly within the script. For dimensions/expressions themself you could use the label-option in the properties.

- Marcus

Re: pivot table help

Thu, 17 Jul 2014 01:39:25 GMT

HI Marcus

thanks for your suggestion

my requirement is previously it was calc now it should get replaced wth Actual % , but unfortuately i am not gettng the expression if i used actual % there help me out

Re: pivot table help

Thu, 17 Jul 2014 02:27:16 GMT

Marcus,

i was deeply confused now the way the chart was created intially

i start creating a new qvw with the same dimensions and same expression bbut my chart looks like this

but the original one was

and also while creating a new qvw i need " Actual %" instead of Calc even that is not getting for me

kindly help me out

Re: pivot table help

Thu, 17 Jul 2014 04:08:59 GMT

Marcus,

Marcus i need your help on how to rename the coumn "Calc" to "Actuals%". As you said that expression in cal dimension , i couldnot get the exact result though. So, can you guide me