Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Nolan
Contributor III
Contributor III

Variable Extension and Pick Function

Good Afternoon,

https://extendingqlik.upper88.com/tag/variable/#:~:text=One%20of%20the%20main%20uses,or%20measures%2....

We are using the variable extension and pick function shown above to have 6 different options for a KPI on our sheet. There are 6 different toggle buttons to flip between them. Everything is working perfectly, except one of our options is in Sales ($) and the rest of the 5 are all in units. Is it possible to change the formatting so only the first option shows as $?

 

The KPI expression is:

pick(vDashboard,
Sum([ TD Quota]),
Sum([ FFX Quota]),
Sum([IV  Quota]),
Sum([Product Quota]),
Sum([Base Quota]),
Sum([HSM Quota]))

 

There is obviously the number formatting drop down, but as far as I can tell whichever option you pick then applies to all 6 of the variations. 

 

Any advice is much appreciated.

Nolan

Labels (5)
1 Solution

Accepted Solutions
skamath1
Creator III
Creator III

You were missing one comma in the  expression  num(Sum([HSM Quota])'#,###.00')

Below is the correct expression..

pick(vDashboard,
num(Sum([TD Quota]),'$#,###.00'),
num(Sum([FFX Quota]),'#,###.00'),
num(Sum([IV Quota]),'#,###.00'),
num(Sum([Product Quota]),'#,###.00'),
num(Sum([Base Quota]),'#,###.00'),
num(Sum([HSM Quota]),'#,###.00')
)

View solution in original post

7 Replies
skamath1
Creator III
Creator III

you can use the num function with the sum.

num(sum(Sales),'$#,###.00') )

 

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Formatting...

Nolan
Contributor III
Contributor III
Author

Thank you for the response!

 

I have changed the Formatting drop down to 'Number' and added this to the expression:

pick(vDashboard,
num(Sum([TD Quota]),'$#,###.00'),
Sum([FFX Quota]),
Sum([IV Quota]),
Sum([Product Quota]),
Sum([Base Quota]),
Sum([HSM Quota]))

 

Unfortunately the first toggle still appears as Units. Is this the correct notation? Any other suggestions? 

skamath1
Creator III
Creator III

add the num to other expressions of sum

like num(sum( [yourfieldname],'#,##0')  

 

also check the property of the number expression as below.

skamath1_0-1613496766651.png

 

Nolan
Contributor III
Contributor III
Author

I have tried this and am still getting an error: 'error in expression: ")" expected' 

I believe my syntax is correct, so not sure what the problem is. Any advice?

 

pick(vDashboard,
num(Sum([TD Quota]),'$#,###.00'),
num(Sum([FFX Quota]),'#,###.00'),
num(Sum([IV Quota]),'#,###.00'),
num(Sum([Product Quota]),'#,###.00'),
num(Sum([Base Quota]),'#,###.00'),
num(Sum([HSM Quota])'#,###.00')
)

skamath1
Creator III
Creator III

You were missing one comma in the  expression  num(Sum([HSM Quota])'#,###.00')

Below is the correct expression..

pick(vDashboard,
num(Sum([TD Quota]),'$#,###.00'),
num(Sum([FFX Quota]),'#,###.00'),
num(Sum([IV Quota]),'#,###.00'),
num(Sum([Product Quota]),'#,###.00'),
num(Sum([Base Quota]),'#,###.00'),
num(Sum([HSM Quota]),'#,###.00')
)

Nolan
Contributor III
Contributor III
Author

sorry for the silly mistake! 

 

Thank you I have now put in the correct expression and I have changed the Number Formatting to measure expression. However when I use the Toggles the number formats are all still the same:

Nolan_0-1614015854669.png

(1st KPI which should be $)

Nolan_1-1614015918425.png

(2nd KPI which should be #)

skamath1
Creator III
Creator III

Is it possible to attach a sample qvf file to really look at the chart object and expressions? otherwise it is difficult to understand the issue.