topic help with an expression in QlikView

help with an expression

Anonymous — Wed, 19 Jan 2011 19:57:33 GMT

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

Anonymous — Wed, 19 Jan 2011 22:14:49 GMT

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

Wed, 19 Jan 2011 22:39:10 GMT

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

Anonymous — Wed, 19 Jan 2011 22:58:14 GMT

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

Anonymous — Thu, 20 Jan 2011 00:39:53 GMT

Please can anybody help me with this?

help with an expression

johnw — Thu, 20 Jan 2011 01:23:48 GMT

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

Anonymous — Thu, 20 Jan 2011 02:01:35 GMT

how do i attach my qlikview file??

help with an expression

johnw — Thu, 20 Jan 2011 02:16:58 GMT

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

Anonymous — Thu, 20 Jan 2011 02:18:59 GMT

Here is the file

help with an expression

johnw — Thu, 20 Jan 2011 02:46:33 GMT

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

Anonymous — Thu, 20 Jan 2011 03:07:32 GMT

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

Anonymous — Thu, 20 Jan 2011 03:17:54 GMT

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

johnw — Thu, 20 Jan 2011 18:32:33 GMT


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.