topic Add coloumn in row in QlikView

Add coloumn in row

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

Hi Team,

I Have a data like this.

I want to add two additional rows into each LOB like Total and %.Please find below image.

In total 0.66 menas 66%

0.81 means 81%

Re: Add coloumn in row

tresB — Tue, 04 Apr 2017 12:39:01 GMT

Could you provide a sample qvw?

Re: Add coloumn in row

sunny_talwar — Tue, 04 Apr 2017 12:39:40 GMT

Would you be able to share a sample to try this out?

Re: Add coloumn in row

MK_QSL — Tue, 04 Apr 2017 12:52:11 GMT

Use like this..

Don't know how you got 0.66 and 0.81

Re: Add coloumn in row

MK_QSL — Tue, 04 Apr 2017 12:54:20 GMT

Change the 2nd expression as below

=Pick(Dim,

Sum(Present),

SUM({1}Present),

Num(SUM(Present)/SUM({1}Total <LOB> Student),'#0%')

)

Re: Add coloumn in row

MK_QSL — Tue, 04 Apr 2017 12:54:45 GMT

84/112 = 75% not 66%

Re: Add coloumn in row

karthiksrqv — Tue, 04 Apr 2017 12:56:32 GMT

Hi,

I don't think we can have totals come up as a percentage when the data-rows are in numbers.

One way could be to enable sub-totals and to then use the title to show the overall percentage of present. This way a user can select a particular LOB and see the % if required, and the sub-totals will always be there.

Re: Add coloumn in row

gireesh1216 — Tue, 04 Apr 2017 12:59:06 GMT

Hi Please share screen shot(UI+SCRIPT)...I have personal edition only...

Re: Add coloumn in row

gireesh1216 — Tue, 04 Apr 2017 13:01:21 GMT

Hi Bro..

This is sample data.I am not calculated %.Manualy Cteated.Please suggest me any idea...

Re: Add coloumn in row

MK_QSL — Tue, 04 Apr 2017 13:03:24 GMT

This is what I have used in Script..

Data:

Load * Inline

[

LOB, Branch, Student, Present

B.TECH, IT, 25, 24

B.TECH, EEE, 30, 10

B.TECH, ECE, 27, 20

B.TECH, CSE, 30, 30

INTER, MPC, 12, 10

INTER, CTC, 25, 20

];

Dim:

LOAD * Inline [

Dim

];

Now create a Pivot Table

Dimension

1) LOB

2) =Pick(Dim, Branch, 'Total', '%')

Expression

=Pick(Dim,

Sum(Student),

SUM({1<LOB,Branch>}Student),

Num(SUM(Student)/SUM({1}Total <LOB> Student),'#0%')

)

=Pick(Dim,

Sum(Present),

SUM({<LOB, Branch>}Present),

Num(SUM(Present)/SUM({1}Total <LOB> Student),'#0%')

)

Re: Add coloumn in row

gireesh1216 — Tue, 04 Apr 2017 13:08:39 GMT

Thanks Mnaish

I will Try

Re: Add coloumn in row

Anonymous — Tue, 04 Apr 2017 13:10:28 GMT

A bit of a workaround by calculating the percentage present at LOB level in the script and then utilising dimensionality in the chart expression.

data:
load * inline [
LOB,Branch,Students,Present
B.TECH,IT,25,24
B.TECH,EEE,30,10
B.TECH,ECE,27,20
B.TECH,CSE,30,30
];

test:
load LOB,
num(sum(Present)/sum(Students),'##0.00%')as %present
resident data
group by LOB;

left join(data)
load *
resident test;
drop table test;

Then in a pivot table use LOB and branch as dimensions with just sum(students) and sum(present) as expressions and tick the show partial sums on the presentation tab to get the totals. Finally the expression for the percentage is =if(dimensionality()=0,only(%present))

It's not quite in the format you wanted but hopefully it may be useful for you.

Re: Add coloumn in row

gireesh1216 — Tue, 04 Apr 2017 13:16:26 GMT

Its working fine

Thnak you somuch