topic Re: Help need in Plots value in chart in QlikView

Help need in Plots value in chart

Anonymous — Wed, 04 Feb 2015 09:17:07 GMT

Hi All,

With below details I need to create a chart that need to have 2 category like HIGH & Low

if sum(TRAFFIC)/Hr>2 =High

if sum(TRAFFIC)/Hr<2=Low

I need to plot this value at suitable chart. Kindly share your inputs

DATE_TIME, TYPE
01:01:2015 00:00:00,TRAFFIC
01:01:2015 01:50:00,ERROR
01:01:2015 01:55:00,TRAFFIC
01:01:2015 02:00:00,TRAFFIC
01:01:2015 02:35:00,TRAFFIC
01:01:2015 03:00:00,ERROR
01:01:2015 03:10:00,TRAFFIC
01:01:2015 03:50:00,TRAFFIC
01:01:2015 04:00:00,ERROR
01:01:2015 04:10:00,TRAFFIC
01:01:2015 04:15:00,TRAFFIC
01:01:2015 04:20:00,TRAFFIC

Re: Help need in Plots value in chart

morganaaron — Wed, 04 Feb 2015 09:50:52 GMT

You'll need a dimension that rounds your date/time to the nearest hour, maybe split this field out into date and time in your script and then use the Hour(TimeField) function as your dimension. You'd then want to use the count function, so:

If(Count({<TYPE={'TRAFFIC'}>}TYPE)>2, 'HIGH', 'LOW')

That would then give you whether traffic was high or low for every hour you have in your data.

If you want simply a count of how many 'highs' and 'lows' there would be over all the hours you'll have, you'll need to take the above idea and use an aggr statement splitting by your hour field.

Edit: See attached for an example.

Re: Help need in Plots value in chart

avinashelite — Wed, 04 Feb 2015 10:53:16 GMT

Hi,

A Simple approach is to Highlight the High and Low value with RED and GREEN background .

Go to >expression> click on +> background then

if( sum(TRAFFIC)/Hr>2,RED(),GREEN())

Re: Help need in Plots value in chart

Anonymous — Thu, 05 Feb 2015 09:11:49 GMT

Hi Aaron,

Thanks for your valuable inputs. Though it helped me to get the idea, however I am having few issues in achieving the result.

My requirement like I need to classify High and Low based on Traffic which is 50.

For Ex If I add Hour as a Dimension, though individually it having the value less than 50 ,when I am summing up it crossing more than 50 at Low

All I need to show is only the values less than 50

DATE_TIME, TYPE,Value
01:01:2015 00:00:00,TRAFFIC,1
01:01:2015 01:50:00,ERROR,5
01:01:2015 01:55:00,TRAFFIC,6
01:01:2015 02:00:00,TRAFFIC,7
01:01:2015 02:35:00,TRAFFIC,5
01:01:2015 03:00:00,ERROR,10
01:01:2015 03:10:00,TRAFFIC,12
01:01:2015 03:50:00,TRAFFIC,14
01:01:2015 04:00:00,ERROR,1
01:01:2015 04:10:00,TRAFFIC,4
01:01:2015 04:15:00,TRAFFIC,2
01:01:2015 04:20:00,TRAFFIC,25

Re: Help need in Plots value in chart

morganaaron — Thu, 05 Feb 2015 09:27:37 GMT

Just to clarify what you're saying, is it when you sum the traffic within an hour that if it's over 50 it's counted as High, and under 50 counted as low? Or by 50 are you referring to time?

Re: Help need in Plots value in chart

Anonymous — Thu, 05 Feb 2015 09:35:24 GMT

Yes ,if I sum the TRAFFIC with in hour I need to classify based on 50. Here 50 is benchmark.

Let me know if I am not clear still

Re: Help need in Plots value in chart

morganaaron — Thu, 05 Feb 2015 09:42:57 GMT

I changed the resident load to be a sum of this "value", then it's just a case of using this field as your counter in your figures.

See attached.