topic Re: obtained / total values in textbox in QlikView

obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 07:39:51 GMT

I have data in sheet like this

products	obtained	total
p1	1200	10000
p2	1000	20000
p3	200	30000
p1	1200	10000
p2	1400	20000
p3	600	30000

here if i get sum of products then p1 shows 2400 p2=2400 and p3=800 .. so here i get 2400 top value against twp products ..

so i want to get top values in textbox and want like this in textbox

p1 (2400/10000) p2 (2400/20000)

how i do this ?

Re: analysis in textbox

petter — Wed, 04 Apr 2018 07:58:20 GMT

It should be something like this:

='p1 (' & Sum({<products={'p1'}>} amount) & '/' & Sum(TOTAL Sum({<products={'p1'}>} amount) & ') p2 (' & Sum(Sum({<products={'p3'}>} amount) & '/' & Sum(TOTAL {<products={'p3'}>} amount) & ')'

Re: analysis in textbox

capriconuser — Wed, 04 Apr 2018 08:13:38 GMT

hi. thankus for your response

but here i dont want to hard code p1, p2 .. i want to get through column name

and also this shows an brackets error and nested aggregation not allowed

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 08:21:47 GMT

How p1 2400?

How p2 2400?

Also when you are showing TOTAL then how this total coming?

Are p1, p2, p3 have any other common field or parent field?

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 08:33:28 GMT

because p1 has 2 values 1200 and 1200=2400 and

p2 has 1000 and 1400 =2400

and yes there is another sheet in excel which has common filed products

check attached sheet

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 08:53:52 GMT

any update

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 09:02:21 GMT

Use this expression in text box..

=Concat({<products = {"=Rank(SUM(obtained))<=2"}>}DISTINCT Aggr(products & ' ' & '(' & SUM(obtained) & '/' & SUM(Distinct total) & ')',products),CHR(10))

This is the output.

Re: analysis in textbox

petter — Wed, 04 Apr 2018 09:04:42 GMT

It should be more like:

='p1 (' & Sum({<products={'p1'}>} amount) & '/' & Sum(TOTAL {<products={'p1'}>} amount) & ') p2 (' & Sum{<products={'p3'}>} amount) & '/' & Sum(TOTAL {<products={'p3'}>} amount) & ')'

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 09:18:55 GMT

why this shows red line?

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 09:20:07 GMT

Don't worry about red lines.. Just check at the top left expression OK is there or not..

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 10:13:24 GMT

OK this work properly but WILL you please why you use rank .. and will you please explain your whole query

i unable to understand

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 10:16:34 GMT

I used concat function to concatenate

product, sum of obtained and sum of total.

rank i have used to get only top 2 products...

to learn comprehensive set analysis, enrol my below course on udemy.

https://www.udemy.com/become-an-expert-of-qlik-set-analysis/learn/v4/overview

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 10:23:16 GMT

only top 2 products? what if we have more than 2 then ? we suppose not to do that manually change rank ...

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 10:30:35 GMT

This is your question

so here i get 2400 top value against twp products ..

So I considered you need top 2. Let me know what is the logic you want.. I can suggest new expression accordingly.

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 10:54:59 GMT

means this is not necessary that only two products i gave only example here .. this may be more than 2 also or may be 1 or may be 3 means ..

rest is same only here i try to clear that this is not necessary only two products may be these is 4 products with same values

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 11:11:06 GMT

when you are saying 2 products, 3 products... but what is the logic of showing them on text box..

right now you are showing 2 products out of 3 ... so why you are showing only 2 and not all 3..

what is the logic?

are you showing only those products which are crossing 2400 ???

if yes the use

=Concat({<products = {"=SUM(obtained)>=2400"}>}DISTINCT Aggr(products & ' ' & '(' & SUM(obtained) & '/' & SUM(Distinct total) & ')',products),CHR(10))

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 11:33:23 GMT

want to show those who have highest values .. or if there is 2 or 3 or so on product with same highest values than want to show accordingly

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 11:39:40 GMT

then use this..

=Concat({<products = {"=SUM(obtained) = Max(TOTAL Aggr(SUM(obtained),products))"}>}DISTINCT Aggr(products & ' ' & '(' & SUM(obtained) & '/' & SUM(Distinct total) & ')',products),CHR(10))

Re: obtained / total values in textbox

capriconuser — Wed, 04 Apr 2018 11:48:18 GMT

thanks so much

Re: obtained / total values in textbox

MK_QSL — Wed, 04 Apr 2018 11:54:21 GMT

you can also use

=Concat({<products = {"=Rank(SUM(obtained),1,1)=1"}>}DISTINCT Aggr(products & ' ' & '(' & SUM(obtained) & '/' & SUM(Distinct total) & ')',products),CHR(10))