Two Conditional Statements

[valdezdj]

Is there any way to overlap or combine conditional statements? Here's the situation, I have bar chart and I would like to segregate each bar and then have conditional values for each one. So, we are tracking software licenses and each software has a max number of licenses. Each bar is a type of software so I would like to set each bar to a certain condition varying on the number of licenses that we have purchased. Thanks for the help in advance.

[JuanC]

I am not sure I completely understand your scenario. Can you post what your data looks like and a picture/description of the chart you are trying to achieve?

JuanC

[valdezdj]

Sorry its a bit confusing.

I already have the chart created (PowerGadgets Creator) and so I have 12 bars that display a number of software installs and the type of software for each bar. For example, I have Flash is one bar and then MS Project is another with the number of installs that we have here. What I want to do is have a conditional statement for each bar depending on the number of licenses that we have purchased and the number of installs. So if we have 90 licenses of Flash and we have 65 installs then the bar would be green but if it went up to  lets say 80 then the bar would turn yellow and if it hit 90 or above the bar would be red. But it would be different for each bar because we purchase different amounts of licenses. I hope this clears things up. And maybe I just need to do it in the PowerShell environment. Let me know. Thanks,valdezdj

[marco.shaw]

I think I understand...  You will not be able to add such logic via the creator.  You will have to build a PowerShell script to do the calculations, then dynamically create your out-chart parameters.

If you can give me a simple example of your data (initial format also), I can write something that should get you at least started...

[IGSFX]

We have an interim hotfix and a sample that can help you. Please contact us at support at powegadgets dot com for further details.

[valdezdj]

Thanks JuanC for the script but this is what I have as a gadget and I don't need two columns, I guess the title of the original post is incorrect. I've attached a picture of the gadget. So what I'm looking for is, when I put a number of maximum licenses that we have purchased for each of the columns then they should change colors as they are getting closer to the maximum number. If one of the columns gets within let's say 5 then it should turn red, if one of the columns get within 10 then yellow and anything below that should be green. As you can see I have them as multiple colors for right now until I or someone else can figure out how to do this. I hope this makes more sense. I'm pulling from a SQL server to get these numbers automatically but I just want the max number of licenses to be manual. Thanks for all the help. 

[JuanC]

Just to provide context for others reading this thread, these were our scripts to try to answer the original question

DataLicense.ps1 (fake script that returns Purchased and InUse licenses)----------------------------function buildObject($product,$purchased,$inuse){ $obj = new-object System.Object add-member -inputobject $obj -membertype NoteProperty -Name Product -value $product add-member -inputobject $obj -membertype NoteProperty -Name Purchased -value $purchased add-member -inputobject $obj -membertype NoteProperty -Name InUse -value $inuse $obj}

buildObject "Flash" 12 7buildObject "Word"  20 18buildObject "Excel" 15 2buildObject "FreeHand" 7 4

LicenseChart1.ps1----------------------------$condition = {   $ratio = $_.InUse/$_.Purchased;   if ($ratio -le 0.25) {   0;   } else {   if ($ratio -le 0.65) { 1;   } else { 2;   } }}

DataLicense | out-chart -label Product -values InUse,Purchased -conditionalattributes_0_color green -conditionalattributes_0_text "Low Use" -conditionalattributes_1_color blue -conditionalattributes_1_text "Medium Use" -conditionalattributes_2_color red  -conditionalattributes_2_text "High Use"-condition $condition -conditionalattributes_0_series 1 -conditionalattributes_1_series 1 -conditionalattributes_2_series 1 -series_0_color "#70404040" -galleryattributes_overlap true -legendbox_itemattributes_-series_visible false

Interesting points about this script

1) Note that the condition does not return a boolean but instead it returns an integer. We will use the conditional_attribute returned by this function

2) We are assigning a color, label and affected series to each conditional attribute

3) Because we wanted to show both the number of purchased licenses and how many are in use, we are setting the overlap property to true to get one series on top of the other. Note that we are setting a transparent color to show the InUse series

4) We are using a hack to hide both series (InUse and Purchased) from the legend box as the colors are not in a per-series basis.

1 and 4 will only work if you are using a newer PowerGadgets build. This funtionality will be supported in our next service pack and we can make it available to anyone interested in this scenario.

LicenseChart2.ps1 (modified according to valdezdj last post)----------------------------$condition = {   $left = $_.Purchased - $_.InUse;    if ($left -ge 10) { 0; } else { if ($left -ge 5) {   1; } else {   2;    } }}

DataLicense | out-chart -label Product -values Purchased -conditionalattributes_0_color green -conditionalattributes_0_text "More than 10" -conditionalattributes_1_color yellow -conditionalattributes_1_text "Between 5 and 10" -conditionalattributes_2_color red  -conditionalattributes_2_text "Less than 5"-condition $condition -legendbox_itemattributes_-series_visible falseChanges from previous script

1) We are only charting the Purchased column although the condition still uses both Purchased and InUse

2) We removed the setting for all conditional attributes because the chart now has only 1 series

 Hope this helps

JuanC

[valdezdj]

JuanC,

Very good, very nice! But I am getting one error, it says Property -series not found. It still displays the chart but there are not red, yellow, or green, I'm wondering if its because of this error. Maybe because I don't have the latest service pack??? If that's the case then I'll just wait for it to be released. Thanks.

[JuanC]

If you are getting that error and the colors are not red,yellow and green then you do not have our most recent build. I just sent you an email with details on how to get it.

JuanC

[valdezdj]

Cool got it thanks. It works now with the colors. So I have another question, man if I could pay you I would. Ok, so how do I get the results to automatically show up in the DataLicense.ps1. Since I'm pulling from an SQL database, I've tried a few things but they didn't work. I tried to set a variable to the invoke-sql statement and then on the buildobject I put the variable there to post the results but that had many errors. I need these results to be dynamic instead of static. I don't care about purchase for now but i do care about in use. Thanks man, I really appreciate your help.

[JuanC]

There is an optimization in invoke-sql that could be causing problems, please try something like

invoke-sql -server SQLServer1 -database TestDB -sql "SELECT Purchased,InUse,ProductName FROM Table" | select Purchased,InUse,ProductName | out-chart ...

The extra select cmdlet between invoke-sql and out-chart might fix this issue. If it does not help please post the SQL statement you are writing and some information about the table where these values are stored.

JuanC