Counting total outside of the table

[Anonymous]

Hi,

The last column of the table is showing the calculated results of an inspection. I put the calculation in a variable. The colors red, orange and green is depending an a range value:  above 9, between 10 and 0, and 0...

I want to count the amount of inspections with the color red, the amount with the color orange and the amount with the color green.

First I made 3 columns and shows the total value in the top.

But now the wanted to see the value separate in or a Pie  (of 3 pieces)  of a textbox.

When I use the variable in a calculation it's always marking that the expression is wrong, but not saying in the top. Evenso no results

.

I also tried to put and = in front of the variabele, but its also giving no results

count({$< =$(vStrafPtTotPo)>9}>}Debtornr)

What am i doing wrong?

Kindly regards,

Jan

[jonathandienst]

Assuming that $(vStrafPtTotPo) returns a valid field name, then

=Count({<[$(vStrafPtTotPo)] = {">9"}>} Debtornr)

If the variable is an expression, then

=Count(If($(vStrafPtTotPo) > 9, Debtrnr))

[Anonymous]

Hi Jonathan,

I needed some time to test things, thats why my answer is delayed.

I tried your answer in different ways and unfortunately there are no results. The variable is an expression.

I will put more information.

It is a database of inspection orders. Each order has about 65 records (Majors and minors)

Depending on the Majors and the rules I am calculating the final score.   From this expression I made the variable vStrafPtTot.  When I selected 1 order there is a result. In this example a score of 15

I also made a table where I show all orders sort by inspectiondate. Behind each order is the final score

To calculate the score I made a new variable vStrafPtTotPo ... The same expression but added 'Order='

Now I wanted to count all the orders of 0 points, between 0 and 10 and above 9 points.

I wanted to show the counting result in a pie.

I think I cannot use the table to try your expession, but I tried it.

The first column is the expresion, the second the variable vStrafPtTotPo, the third vStraftPtTot and the last your expression =Count(If($(vStrafPtTotPo) > 9, Debtrnr))

I know I have to work in another way, but have no idea wich way to go!

I wanted to attach the expression in a txt file but I didn't find a way to upload it.

I will post it in the next message.

Kindly regards, Jan

[sunny_talwar]

May be try this

=Count({<Debtnr = {"=$(vStrafPtTotPo) > 9"}>} Debtrnr)

or this

=Count(Aggr(If($(vStrafPtTotPo) > 9, Debtrnr))

[Anonymous]

Hi Sunny,

The first expression is giving 0 as result

The second an error.

I also put the expression in a textbox and get the result:

Thnx

[Anonymous]

The variable vStrafPtTotPo:  (vStrafPtTot is the same without 'OrderNr=')

(if((sum({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,2,3,4} ,Description={'Hoofdeis'}>}NprNumPointsRequired)-(count({$<PointNr ={1.8},ScoreID={3},ArticleCode={601119}>}ScoreName)))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,4} ,Description={'Hoofdeis'}>}ScoreName))

,

((sum({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,2,3,4} ,Description={'Hoofdeis'}>}NprNumPointsRequired)-(count({$<PointNr ={1.8},ScoreID={3},ArticleCode={601119}>}ScoreName)))

-

(count({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,4} ,PointNr -={'1.8'}, Description={'Hoofdeis'}>}ScoreName)))*3,

0))

+

(if((sum({$<OrderNr=, MajorName=, MajorSortorder=-{1}, ScoreID={1,2,3,4} ,Description={'Hoofdeis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=,MajorSortorder=-{1}, ScoreID={1,4} ,Description={'Hoofdeis'}>}ScoreName))

,

((sum({$<OrderNr=, MajorName=, MajorSortorder=-{1}, ScoreID={1,2,3,4} ,Description={'Hoofdeis'}>}NprNumPointsRequired))

-

(count({$<OrderNr=, MajorName=, MajorSortorder=-{1}, ScoreID={1,4} , Description={'Hoofdeis'}>}ScoreName)))*3,

0))

+

// eisen

//eis mj1

if((sum({$< OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired)-(count({$<PointNr ={1.9},ScoreID={3},ArticleCode={601119}>}ScoreName)))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, MajorSortorder={1}, ScoreID={2,3} , Description={'Eis'}>}ScoreName))-(count({$<PointNr ={1.9},ScoreID={3},ArticleCode={601119}>}ScoreName)))

>1, 1,0),0)

//eis mj2

+

if((sum({$<OrderNr=,  MajorName=, MajorSortorder={2}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={2}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={2}, Description={'Eis'}>}ScoreName)))

>=5, 2,1),0)

//eis mj3

+

if((sum({$< OrderNr=, MajorName=, MajorSortorder={3}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={3}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={3}, Description={'Eis'}>}ScoreName)))

>=4, 2,1),0)

//eis mj4

+

if((sum({$<OrderNr=,  MajorName=, MajorSortorder={4}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={4}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={4}, Description={'Eis'}>}ScoreName)))

>=3, 2,1),0)

//eis mj5

+

if((sum({$< OrderNr=, MajorName=, MajorSortorder={5}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={5}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={5}, Description={'Eis'}>}ScoreName)))

>0, 1,0),0)

//eis mj6

+

if((sum({$<OrderNr=,  MajorName=, MajorSortorder={6}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={6}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={6}, Description={'Eis'}>}ScoreName)))

>0, 1,0),0)

//eis mj7

+

if((sum({$< OrderNr=, MajorName=, MajorSortorder={7}, ScoreID={1,2,3,4} ,Description={'Eis'}>}NprNumPointsRequired))

>

(count({$<OrderNr=, MajorName=, MajorSortorder={7}, ScoreID={1,4} ,Description={'Eis'}>}ScoreName))

,

If(((count({$<OrderNr=, MajorName=, ScoreID={2,3} ,MajorSortorder={7}, Description={'Eis'}>}ScoreName)))

>0, 1,0),0)

[sunny_talwar]

Would you be able to share your qvw to check this out?

[Anonymous]

Hi Sunny,

This is a part of the qvw

Thanks !

https://community.qlik.com/servlet/JiveServlet/downloadBody/19707-102-1-26791/TestScore.qvw

[sunny_talwar]

Are you expecting to see 12 for the sample data?

=Count(DISTINCT {<OrderNr = {"=$(vStrafPtTotPo) > 9"}>}OrderNr)

[Anonymous]

I only want to make a pie whit 3 counters... How many orange (between 0 and 10 points) How many red (Above 9 point) and how many green (0 points)

So in the screenshot above 16 green, 6 orange and 2 red

I don't have to see it in a table  (or it must be 3 records) , my final is a Pie

I also uploaded the qvw file

Thanks