topic Re: Stacked Bar chart and offset in QlikView

Stacked Bar chart and offset

Tue, 13 Jan 2015 17:53:12 GMT

Dear Community,

I'm trying to created a stacked bar chart using data that captures activity over a day. My raw data has a start time and a duration. I want to stack these in a single column but offset each section so that it starts on the start date and is the size of the duration.

I think that I am close but my offset formula is using the "above" expression that I don't 100% understand.

=min(Tasks.DateAssigned)-floor(Min(Tasks.DateAssigned))-below(min(Tasks.DateAssigned)-floor(Min(Tasks.DateAssigned)))

Thank you for your help

Re: Stacked Bar chart and offset

MarcoWedel — Wed, 14 Jan 2015 00:11:39 GMT

Hi,

another solution could be to create calculated dimensions for date and time parts of the start timestamp and an expression for the bar offsets like

=RangeSum(Frac(Start),-Above(Frac(Start))-Above(Duration))

that calculates the start position of each stacked bar segment.

rangesum() is used because Frac(Start)-Above(Frac(Start))-Above(Duration) would not deliver a value for the first segment of each bar (no above row present). Above() returns the value of the table row before ("above") the current row.

hope this helps

regards

Marco

Re: Stacked Bar chart and offset

Wed, 14 Jan 2015 11:17:46 GMT

Hi Marco,

This is so close to exactly what I need. I only have one issue:

I want to apply min and max times so that the graph only shows a working day but when I apply these in axis it doesn't appear to work for the minimum?

Also I don't suppose you could provide a brief explanation of how it works so that I might be able to understand things better and trouble shoot if any issues come up.

Many thanks.

Best regards,

Drew

Re: Stacked Bar chart and offset

MarcoWedel — Wed, 14 Jan 2015 22:51:34 GMT

Hi,

maybe like this:

did you deselect the "forced 0" check box?

As I have only start timestamps and durations in the datamodel, I used

DayName(Start)

as first calculated dimension to extract the date part from the timestamp. DayName rounds of the decimal timestamp value of the start field to full dates and formats the resulting integer as date.

I then added

Time(Frac(Start))

as secondary calculated dimension. Frac(Start) extracts the fractional part (containing the time value) of the decimal timestamp value.

Time() formats the resulting number (between 0 and 1) as time.

Since my test data only has one duration for each start timestamp, I just used "Duration" as the only expression.

The bar offset expression (under the dutration expression) defines an offset for each bar as

=RangeSum(Frac(Start),-Above(Frac(Start))-Above(Duration))

which means that one bar starts at a time after the previous bar ends that is defined by "starttime-(starttime+duration of the previous bar)"

hope this makes sense and helps

regards

Marco

Re: Stacked Bar chart and offset

Fri, 16 Jan 2015 16:23:52 GMT

Hi Marco,

This is brilliant - I am still having a some slight issues with my graph, I think it is because my data is very complex so I am using aggr and mins to get my durations and start times - the graph is plotting quite closely to correct figures but is slightly out. Can you spot anything that is incorrect?

Duration Expression

Offset Expression

The reason for the min and aggr is due to multiple lines for each "Job"

Many thanks for all your help.

Drew