Forum Discussion
Using Switch With Calculated Variable
Greetings.
I am trying to use a SWITCH statement to produce a result for a card. I want to base the result off of a percentage that is calculated. If the percentage is >=.01 then I want the original percent that was calculated. If it is less than .01 then I want it to display "<.01". If it is blank I want it to return "Blank". Here is what I tried but it keeps giving me errors.
new test =
Switch(
True(),
divide(count(sheet1[review not completed]),count(sheet1[review date] >=.01, divide(count(sheet1[review not completed]),count(sheet1[review date],
divide(count(sheet1[review not completed]),count(sheet1[review date] <.01, “<.01”,
“Blank”
)
What am I doing wrong?
Hi Nick555 ,
To me it looks like a syntax issue. Can you try this?
Measure = SWITCH ( TRUE(), DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) >= 0.01, DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ), DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) < 0.01, "<.01", BLANK() )
If this does not work, feel free to share some sample data and I can have a deeper look into it 🙂/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
6 Replies
Hi Nick555 ,
To me it looks like a syntax issue. Can you try this?
Measure = SWITCH ( TRUE(), DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) >= 0.01, DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ), DIVIDE ( COUNT ( sheet1[review not completed] ), COUNT ( sheet1[review date] ) ) < 0.01, "<.01", BLANK() )
If this does not work, feel free to share some sample data and I can have a deeper look into it 🙂/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Thanks! The syntax definitely works though the result doesn’t seem to be what I was expecting. I was expecting the Pct Not Complete to show “Blank” but instead it still shows <.01%.
So I’m trying to put the dashboard below together. The bottom right card ,Pct Not Complete) was expecting to show “blank” by using the switch coding you fixed for me. I provided some sample data in the hopes maybe we can figure this out. Any ideas will be most appreciated.
Thx
Department
DIVISION-AD
DIVISION-DD LEVEL
review date
Completed date
Review Not Completed
Completed Within 30 Days
City Manager
A
AE
March 4, 2020
1
1
City Manager
A
AE
May 16, 2020
1
1
City Manager
A
AE
July 6, 2020
1
1
City Manager
A
AE
October 6, 2020
1
1
City Manager
A
AF
January 7, 2020
1
1
City Manager
A
AF
January 15, 2020
1
1
City Manager
B
AF
September 23, 2020
1
1
City Manager
B
AG
July 7, 2020
1
1
City Manager-Arts Program
A
AG
March 27, 2020
April 6, 2020
1
City Manager-Arts Program
A
AG
May 27, 2020
May 29, 2020
1
City Manager-Arts Program
A
AH
April 30, 2020
May 15, 2020
1
City Manager-Arts Program
A
AH
December 29, 2020
January 7, 2021
1
City Manager-Arts Program
B
AH
March 11, 2020
April 2, 2020
1
City Manager-Arts Program
B
BN
September 29, 2020
October 1, 2020
1
City Manager-Arts Program
C
BN
March 9, 2020
March 24, 2020
1
City Manager-Arts Program
C
BN
June 9, 2020
June 26, 2020
1
City Manager-Arts Program
C
BN
June 26, 2020
July 8, 2020
1
Communications Office
A
MM
September 29, 2020
October 1, 2020
1
Communications Office
B
NN
September 9, 2020
September 17, 2020
1
Communications Office
B
NN
September 16, 2020
October 15, 2020
1
Communications Office
B
NN
September 28, 2020
October 1, 2020
1
Communications Office
C
LL
June 17, 2020
June 26, 2020
1
Communications Office
C
LL
November 23, 2020
December 9, 2020
1
Communications Office
D
JJ
July 17, 2020
August 5, 2020
1
Hi Nick555,
Hm, I can see why the measure is showing "< 0.01" cause that is how we coded it:
If your calculation returns a value equal or more than 0.01, then return that calculated valueIf your calculation returns a value lowet than 0.01, then return "< 0.01"
ele return blank()
We covered all possible outcomes already in the first two if clauses, meaning we will never return blank(). Can you clarify in which cases we shall return blank()?
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Apologies. You are correct that we covered the outcomes. Result of staring to long at this I'm afraid.
What I was thinking/wanting was that when the card above it (Revicew Not completed count) shows Blank, that this card show blank as well.
