# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## Counting total outside of the table

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

1 Solution

Accepted Solutions
MVP

Not sure how you are getting 16, 6 and 2... but I am getting 69, 42, and 12

12 Replies

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Contributor III

Hi Jonathan,

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

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

MVP

May be try this

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

or this

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

Contributor III

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

Contributor III

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)

MVP

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

Contributor III
MVP

Are you expecting to see 12 for the sample data?

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

Contributor III

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

Tags