topic Re: New column in QlikView

New column

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

Hi All,

Olivier Robin

Anupam Mehta

Pooja Nagaraj

i want to create a new column in Pivot table

REQ: i need one extra column(Total) the total column show always sum up the COunt(New)+count(Old)

if user select any measure for that measure also it should sum count(New)+Count(Old)

Result:

in above example result column should be:

total

A: 2(New)+old(2)

B:1(New)+0(Old)

C:0(New)+1(Old)

Re: New column

olivierrobin — Mon, 03 Sep 2018 12:00:55 GMT

hello

i don't really understand your need ...

in a pivot table, you can add a new column with an expression

such as sum(New)+sum(Old)

count(distinct new) + count(distinct Old)

Is this your need ?

Re: New column

soniasweety — Mon, 03 Sep 2018 12:06:39 GMT

No no.

here i need a Result column Total

that total how it is caluclating like below

if i select any measure you can see count /sum/avg for New and old

so here it should sum the count measure new + count meausre old+ count measure of avg

Re: New column

olivierrobin — Mon, 03 Sep 2018 12:11:45 GMT

can you give some sample input data ?

As I understand you have

Key New Old

A 2 2

B 1 0

C 0 1

and the expected result is

A 4

B 1

C 1

Re: New column

soniasweety — Mon, 03 Sep 2018 12:21:16 GMT

Attached updated Qvw

i want to always sum up and show the total column Net(sum(New)+sum(Old)

Re: New column

soniasweety — Mon, 03 Sep 2018 12:22:44 GMT

yes the result should be above you mentioned ...if one more measure is selected it should sum the only net(Measure only)

Re: New column

olivierrobin — Mon, 03 Sep 2018 12:28:03 GMT

you can't select a measure, you select a key ...

As I understand, you could :

create an inline table with values 'New','Old' (let's say the column name is Choice)

so that user can select one or more values in this list

and your new column would be something like

if(wildmatch(concat(Choice),'*New*),sum(New),0) + if(wildmatch(concat(Choice),'*Old*),sum(Old),0)

Re: New column

soniasweety — Mon, 03 Sep 2018 12:32:21 GMT

its an adhoc report

so user can select the measures and dimensions

if only user selected

Dimesnion:company and type

Measure: Net

result: type New only

result: 350

200

Re: New column

olivierrobin — Mon, 03 Sep 2018 12:38:21 GMT

I did'nt notice that

but

as Old, new is a dimension, if you select one value, it works as desired with doing anything

Re: New column

soniasweety — Mon, 03 Sep 2018 12:42:43 GMT

old new is not diemnsion

type is the dimension : type values are new and old.

now you cannt get the result directly

Re: New column

soniasweety — Mon, 03 Sep 2018 12:44:02 GMT

type always it will disaply new and old but total column only it should sum

Re: New column

olivierrobin — Mon, 03 Sep 2018 12:44:43 GMT

sorry, it was what i meant

if I click on New, you can see the chart shows only that column

Re: New column

dx_anupam — Mon, 03 Sep 2018 12:44:46 GMT

Hi Sony,

I think we can get it using calculated Dimension.

add formula =Aggr(Count(Id),Company) in calculated dimension.and label it as Total.

it might help you achieve desired output. only problem it will be start instead of end.

Regards,

Anupam

Re: New column

soniasweety — Mon, 03 Sep 2018 12:50:32 GMT

No . when user select any other dimension and mesure it will not work

Re: New column

soniasweety — Mon, 03 Sep 2018 12:53:30 GMT

but if i select other measure it should sum the both

it shoudl show new and old

Re: New column

dx_anupam — Mon, 03 Sep 2018 12:55:07 GMT

Hi Sony,

I don't find any issue with it, can you please try and let me know result as its working perfectly fine at my end.

Regards,
Anupam

Re: New column

olivierrobin — Mon, 03 Sep 2018 13:01:22 GMT

the problem is that you try to create a new total column at another level.

Why don't you :

crate the column as sum()+count()+sum()

and add total at every level

so that your column id displayed at each detail level at at the total level ?

Re: New column

soniasweety — Mon, 03 Sep 2018 13:17:56 GMT

can you attch your app?

Re: New column

olivierrobin — Mon, 03 Sep 2018 13:20:07 GMT

1 - add an expression :

2 - add partial sum at "Type" level

Re: New column

soniasweety — Mon, 03 Sep 2018 13:27:38 GMT

its not giving me exact result