Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

obtained / total values in textbox

I have data in sheet like this

productsobtainedtotal
p1120010000
p2100020000
p320030000
p1120010000
p2140020000
p3600

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 ?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Use this expression in text box..

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


Capture.JPG

This is the output.

View solution in original post

19 Replies
petter
Partner
Partner

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) & ')'

capriconuser
Creator
Creator
Author

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

MK_QSL
MVP
MVP

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?

capriconuser
Creator
Creator
Author

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

capriconuser
Creator
Creator
Author

any update

MK_QSL
MVP
MVP

Use this expression in text box..

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


Capture.JPG

This is the output.

View solution in original post

petter
Partner
Partner

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) & ')'

capriconuser
Creator
Creator
Author

Capture.PNG

why this shows red line?

MK_QSL
MVP
MVP

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