topic Re: expanding time range in chart in QlikView

expanding time range in chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikview experts,

I have data records with time interval of 5 minutes. I need to show a stacked line chart with time (e.g. time stamp) as dimension and number of distinct users as expression. This can be easily shown with a simple dimension and expression but I can only show the chart within limited time range (e.g. less than 24 hours). When I changed the interval to different value number of users is different, which might be additional distinct users added to this longer interval. As data is quite a lot it is uneasy to verify if this is a correct assumption, but the number does not seem to be in line with normal behavior and observation.

What is the way to show longer period in the chart but still maintain small interval so that the count distinct doesn't add up to unexpected numbers?

Here is the example of the chart

When interval is changed to show longer period in the chart this is what appears in the chart:

Still new to Qlikview, so any hint to this would be helpful.

Re: expanding time range in chart

sunny_talwar — Thu, 16 Jun 2016 18:29:15 GMT

May be like this:

Sum(Aggr(Count(Distinct UserID), TimeStamp))

Re: expanding time range in chart

Anonymous — Thu, 16 Jun 2016 20:14:11 GMT

Hi,

I tried that expression but it seems now it is aggregating all numbers instead of keeping the number the same within that range. Besides, time range is not extended either unless I change the interval.

I still cannot show extended time range in the chart without changing the interval, but if I changed the interval to show longer time range the number with longer interval is summed, which I don't expect to see.

The intention here is to show longer time range without changing the 5 minutes time stamp interval.

Re: expanding time range in chart

sunny_talwar — Thu, 16 Jun 2016 20:16:02 GMT

How about this:

Sum(Aggr(Count(Distinct UserID), 5MinuteIntervalFieldHere))

Re: expanding time range in chart

Anonymous — Thu, 16 Jun 2016 22:06:31 GMT

Nothing showing up with that expression.

Attached is the example of the file I am using to test.

Re: expanding time range in chart

sunny_talwar — Thu, 16 Jun 2016 22:23:00 GMT

Can you try this as your dimension:

=Aggr(Dual(

( Time((Ceil([Time Stamp],1/IntervalSize)),'hh:mm')),

Ceil(Frac([Time Stamp]),1/IntervalSize)), [Time Stamp])

Re: expanding time range in chart

Anonymous — Fri, 17 Jun 2016 21:31:21 GMT

Hi,

Actually I have already used this similar expression, and tried with both Ceil and Foor .

=Aggr(Dual(

( Time((Ceil([Time Stamp],1/IntervalSize)),'hh:mm')),

Ceil(Frac([Time Stamp]),1/IntervalSize)), [Time Stamp])

It is OK to change the interval, but when I change the interval my chart us different. My intention is actually to keep the interval size at 5 min but would like to show longer period (e.g. 3 days or one week) instead of just merely a few hours on the x dimension. I think the above expression simply allows us to change the interval and round up the value to the nearest interval value)

I'd like to show the first chart above and keep the 5 in interval but showing longer range without using time slider on the X axis.

Re: expanding time range in chart

MarcoWedel — Sat, 18 Jun 2016 23:40:08 GMT

Hi,

one solution could be to use the "show all bars" option of the presentation tab.

(It will be available only after switching to bar chart type, but you can switch back to line chart after applying this setting.)

Another solution could be to use a continuous dimension axis.

You cannot use this option with your current calculated dimension as it delivers no numerical values, just text.

Changing your dimension to

=Timestamp((Ceil([Time Stamp],1/IntervalSize)),'DD-MMM-hh:mm')

however will also deliver numerical values to use with continuous dimension axis:

hope this helps

regards

Marco

Re: expanding time range in chart

Anonymous — Mon, 20 Jun 2016 16:54:45 GMT

HI Marco,

Many Thanks for the tips. This works.

I learn something today.

Re: expanding time range in chart

MarcoWedel — Mon, 20 Jun 2016 18:20:10 GMT

glad it helped.

please close your thread if your initial question is answered.

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: expanding time range in chart

Anonymous — Mon, 20 Jun 2016 19:47:25 GMT

Now I have another requirement, which is to show the Y axis of line chart in percent, similar to Mekko Chart.

When I used

Count({<Latency = {'<600'}, [Throughput (Kbps)] = {'<1000'}>}DISTINCT [Hardware Id])/Count(DISTINCT([Hardware Id]))

and changed the number to integer and selected show in percent under number tab, the total number does not result in 100%. In this example it shows below 100%, and real data I had even more than 100%. Any hint how to simply make it like Mekko chart so that it is always showing 100%.

The expression seems to be correct and theoretically all numbers should add up in 100%. Any clue ?

Re: expanding time range in chart

MarcoWedel — Mon, 20 Jun 2016 20:46:03 GMT

please open a new thread for this new question.

thanks

regards

Marco