topic Re: % of total in QlikView

% of total

kunkumnaveen — Tue, 09 Feb 2016 09:37:03 GMT

Hello All

I need to show % of Total,i mean if suppose my data is like this(thinking to show either in straight table or pviot table)

rawmaterialname suppiler no.of lots

hydrogensulphate birla 10

hydrogensulphate l&t 20

hydrogensulphate hzl 30

sulphate birla 30

sulphate kun 50

sulphate nav 60

now,In my straight table or pivot table i need a column with %of total

dimensions :rawmaterial,suppiler

Table:

rawmaterialname suppiler %of total

hydrogensulphate birla i need some thing like this(10/60)

hydrogensulphate l&t 20/60

sulphate birla 30/140

sulphate kun 50/140

so please help me with expression what should i need to write down in that column....will be any change whether i display it either in straight table or pivot table

thanks

naveen

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 09:58:45 GMT

Try this:

Sum([no.of lots])/Sum(TOTAL <rawmaterialname> [no.of lots])

Re: % of total

kunkumnaveen — Tue, 09 Feb 2016 10:38:54 GMT

Hello Sunny ,thanks for very quick reply ,every thing is working fine expect the the final total well if everything is 100% obsivouly the final total has to be 100% am i right ,i there any option to remove Total only to that particular column please find the attachment

thank naveen

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 11:41:19 GMT

I don't see any attachments

Re: % of total

sfatoux72 — Tue, 09 Feb 2016 11:57:30 GMT

On expression tab, select your expression and modify this option to remove Total for this column

Re: % of total

Tue, 09 Feb 2016 11:59:55 GMT

Can't you just use the relative % tick box in a straight table to give you what you want

Re: % of total

kkkumar82 — Tue, 09 Feb 2016 12:06:42 GMT

Hi Sunny ,

Will the expression you gave will give the expected by naveen ??

I tried but getting differently.

Thanks

Kiran Kumar

Re: % of total

jonathandienst — Tue, 09 Feb 2016 12:07:34 GMT

That option will be % of the total data set. I think the OP wants it by raw material. Sunny has that right.

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 12:10:23 GMT

Isn't this is what is needed?

Re: % of total

kkkumar82 — Tue, 09 Feb 2016 12:10:56 GMT

All,

I earlier tried like this to get what naveen wants

Sum([no.of lots])

Max(aggr( NODISTINCT Sum([no.of lots]),rawmaterialname))

thanks

Kiran Kumar

Re: % of total

kkkumar82 — Tue, 09 Feb 2016 12:18:05 GMT

Hi Sunny,

Apologies for your expression I was mistaken but mean while can you test mine also, I was able to achieve in this way also.

thanks

Kiran Kumar

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 12:21:54 GMT

‌Kiran -

No apologies needed my friend. With regards to the expression, there might be multiple ways to achieve your goals. Personally I try to avoid Aggr() function as much as I can. So I suggested the expression with the TOTAL qualifier .

Re: % of total

kkkumar82 — Tue, 09 Feb 2016 13:20:03 GMT

Sure,

Just curious to know in the expressions

Sum(Total <rawmaterialname> nooflots) and Sum(Total {<rawmaterialname>} nooflots)

second uses set analysis and it is giving a different result, can you explain the first one a little bit more, like is it used for this kind of situations or elsewhere.

Thanks

Kiran Kumar

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 13:40:09 GMT

For sure:

First one is telling which field you would want to use the Total Qualifier on. Let's take the above example

First do a total by rawmaterialname

1) Sum(TOTAL <rawmaterialname> [no.of lots])

Now lets try by supplier

2) Sum(TOTAL <suppiler> [no.of lots])

Finally the overall total

3) Sum(TOTAL [no.of lots])

Re: % of total

kkkumar82 — Tue, 09 Feb 2016 17:34:39 GMT

Crystal Clear .

Many thanks

Kiran Kumar

Re: % of total

sunny_talwar — Tue, 09 Feb 2016 17:35:48 GMT

Awesome

Re: % of total

kunkumnaveen — Wed, 10 Feb 2016 06:31:04 GMT

Hello Byron

unfortunately see that TOTAL MODE option its graded out,i mean its not letting me to select any option of that particular expression ,please have u got any idea how to get away from this grand Total only for this particular column

thank you

naveen

Re: % of total

kunkumnaveen — Thu, 11 Feb 2016 05:46:59 GMT

Hello sunny ,

Thanks for your quick response but sunny wats the grand total we might expect when both the subtotal are showing 100% as far as i think the grand total has to be 100% am i right ,in my case i am getting all subtotal perfect i mean 100% but the grans total is showing about 94% and i am literally not able to expalin to my client when he ask me about that grand total,i tried to get rid of that grand total to the particular column by going in to expression tab------>no total but unfortunately the NO TOTAL check box is graded out i mean its not letting me to check any option ...so sunny is there any solution for this please

thanks

naveen

Re: % of total

jagan — Thu, 11 Feb 2016 06:02:56 GMT

HI Naveen,

That Total Mode will be enabled for Straight Table, I think you are using Pivot table, that is why it is disabled.

Can you attach your expected output so that it is easier to understand.

Regards,

Jagan.

Re: % of total

sfatoux72 — Thu, 11 Feb 2016 09:17:57 GMT

‌Perhaps have you some "no.of lots" without rawmaterialname associated