12 Replies Latest reply: Jan 20, 2011 1:32 PM by John Witherspoon RSS

    help with an expression

    radhika sriramoju

      Hi

      I have an expression sum(util_rate) which has value 0 and > 2

      I need to have Xaxis with the stacked bars IDLE and USED(stacked with runi_devices values).

      so for a selected filter , if sum(util_rate) > 2 then the device is considered as used or else Idle.

      the is expression of count of devices.

      Please help me , in giving the expression for idle and usage , or the best way to achive this.

      Thanks

       

        • help with an expression
          radhika sriramoju

          so i came up with am expression which gives the values i need but not sure how to segment them as a stacked bar chart ,

          =if(sum(Avg(UTIL_RATE)>2),'used','idle') --- (dimension)

          Count (DISTINCT RUMI_DEVICE) --- expresion

          so i need to display idle and used bars(separare bars), segmented by distinct rumi_devices, displaying the count of rumi_devices .

           

          Please help

            • help with an expression

              I would do this as a set analysis.

              For Used, it would be something like this: Count({$ <Usage={'>0'}>} Distinct Rumi_Device)

              For Idle, the Expression would be something like this: Count({$ <Usage={0}>} Distinct Rumi_Device)

              You can stack them or keep them separate by looking at the Style tab under Subtype.

              Thanks,

              Aline

                • help with an expression
                  radhika sriramoju

                  Hi

                  I need to have the above expression in set analysis , and the chart should display something like below, i am sorry for this but iam new to set analysis , i have attached an excel sheet for the graph am looking for , please let me know if you cant open it ..

                  I nee to have if(sum(Avg (UTIL_RATE)>2,'Used','Idle')) in set analysis i guess, i tried replacing the USAGE>0 with if(sum(Avg (UTIL_RATE)>2,'Used','Idle')) but i get an error,

                  [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/2235.rumi_5F00_scratch-_2800_2_2900_.xlsx:550:0]

                   

                    • help with an expression
                      radhika sriramoju

                      Please can anybody help me with this?

                        • help with an expression
                          John Witherspoon

                          I suggest posting a QVW with sample data in it loaded with an inline load. Looking at your Excel file, I don't see anything that looks like raw data. For instance, you already have column for IDLE and IN-USE, and this column appears to have been entered manually. You also have a count, but again, it's just a number. Nothing seems to be being counted. The answer is probably simple, but I really can't understand what you're asking, so I can't answer.

                            • help with an expression
                              radhika sriramoju

                              how do i attach my qlikview file??

                                • help with an expression
                                  John Witherspoon

                                  When you create a post, you should see three tabs - Compose, Options and Preview. On the Options tab is an "Add/Update" button under the heading "File Attachment". Click on that button to upload the file from your computer and attach it to your post.

                                    • help with an expression
                                      radhika sriramoju

                                      Here is the file

                                        • help with an expression
                                          John Witherspoon

                                          OK, I think I have it. Your data appears to only have a single target, so I added a few additional targets to make sure I was getting both Idle and Used targets, and that the targets were stacking within these bars. I did it like this:

                                          Dimension 1 = =aggr(if(sum("AVG(UTIL_RATE)")>2,'Used','Idle'),TARGET)
                                          Dimension 2 = Target
                                          Expression = count(distinct RUMI_DEVICE)

                                          See attached. Hopefully I've understood what you needed, and hopefully it will work in the real application.

                                            • help with an expression
                                              radhika sriramoju

                                              Thank you very much ,

                                              But i dont understand why my app is showing only Idle bar , i copied the same thing , i am attaching my app here , you cannot work on it but atleast you can see the properties i guess.

                                                • help with an expression
                                                  radhika sriramoju

                                                  I have the column UTIL_RATE in my table not AVG(UTIL_RATE) , So i changed the calculated dimension to (removed the double quotes) , but still only idle is showing ..:-(

                                                  =aggr(if(sum(Avg(UTIL_RATE))>2,'Used','Idle'),TARGET)

                                                   

                                                  what could be the possible reason for not displaying used and idle bars separetely?

                                                  Please help...

                                                    • help with an expression
                                                      John Witherspoon

                                                       


                                                      RAMYA_BEGINNER wrote:what could be the possible reason for not displaying used and idle bars separetely?


                                                      It looks like you figured out that you need to replace "AVG(UTIL_RATE)" with "UTIL_RATE" since that's what the column is called in your actual data set. However, since you've also added the Calendar dimension, you need to aggr by that as well.

                                                      =aggr(if(sum(UTIL_RATE)>2,'Used','Idle'),Calendar,TARGET)

                                                      You can't tell that you need this in the sample data set because you have the same exact UTIL_RATE for every day. But you can see it if you, say, change the Calendar dimension to SITE_NAME. Then you can see, for example, that for Santa Clara, 8960_APPS is shown as Used even though sum(UTIL_RATE) is only 1. I assume you'd want this to show as Idle.

                                                      I hope you don't mind, but I'm going to post your private message to me here since I see no reason this shouldn't also be part of this thread:

                                                       


                                                      RAMYA_BEGINNER wrote:Hi
                                                      This is in continuation for the post
                                                      http://community.qlik.com/forums/t/39662.aspx
                                                      thank you for the response for my question , i have an addition to my application , i need to add another line chart which shows , count of rumi devices in 3 lines ,
                                                      line for idle
                                                      line for used
                                                      line for all
                                                      i am able to get lines in the chart with calculated dimension condition for idle and used
                                                      =aggr(if(sum(UTIL_RATE)>2,'Used','Idle'),TARGET)
                                                      i need to combine another condition given below , to get a line displaying all count of all devices
                                                      =aggr(DISTINCT SUM(RUMI_DEVICE),TARGET)
                                                      How do i combine both of these expression into one , if combining is not an option , how can i achive 3 lines in my line chart displaying used,idle,all
                                                      Please let me know...
                                                      Thanks...


                                                      I'd just use three separate expressions, and I'm assuming below that your dimension is again Calendar.

                                                      Total = count(distinct RUMI_DEVICE)
                                                      Used = sum(aggr(if(sum(UTIL_RATE)>2,count(distinct RUMI_DEVICE)),Calendar,TARGET))
                                                      Idle = Total - Used

                                                      That seemed to work for me with your file, though I had te replace Calendar with SITE_NAME in order to verify. And if Calendar isn't your dimension, replace it in the expression above with whatever your dimension actually is.

                                                      To explain what's going on in the "Used" expression, the aggr(...,Calendar,TARGET) groups your data by calendar and target. So if you have Date selected in your Calendar, for each date and target, we sum(UTIL_RATE). If that sum is > 2, then that TARGET is Used for that Date. Therefore we must count the distinct RUMI_DEVICE for that Target. Finally, since TARGET isn't actually a dimension in our chart, we must sum up these counts for all Used TARGETS on that date to get our total Used RUMI_DEVICEs. Hopefully the other two expressions are obvious.