Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Not sure how you are getting 16, 6 and 2... but I am getting 69, 42, and 12
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))
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
May be try this
=Count({<Debtnr = {"=$(vStrafPtTotPo) > 9"}>} Debtrnr)
or this
=Count(Aggr(If($(vStrafPtTotPo) > 9, Debtrnr))
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
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)
Would you be able to share your qvw to check this out?
Hi Sunny,
This is a part of the qvw
Thanks !
https://community.qlik.com/servlet/JiveServlet/downloadBody/19707-102-1-26791/TestScore.qvw
Are you expecting to see 12 for the sample data?
=Count(DISTINCT {<OrderNr = {"=$(vStrafPtTotPo) > 9"}>}OrderNr)
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