topic Re: Aggr in Gantt chart returns wrong data for dates in QlikView

Aggr in Gantt chart returns wrong data for dates

Thu, 16 Jun 2016 10:54:50 GMT

I'm using a horizontal bar chart as a gantt chart (see here) where the first dimension is the user (only displays if only one user is selected) and the second one consists of the date, the time and the event (so every event will have their own bar). See example dataset below, time in HHMMSS format, the expression is simply 1 (as one day).

LOAD * INLINE [

user, date, time, event

Alice, 12.04.2016, 122036, Purchase

Alice, 12.04.2016, 122500, Contact

Alice, 13.04.2016, 163521, Purchase

];

This creates three bars - one for the purchase on the 12th, one for the contact on the 12th and one for another Purchase on the 13th. I would like to color every bar (change background of expression) red that has a contact on the same day.

I tried this formula which looks completely correct to me and i cant find any logical issues with it. It should create a virtual table where the expression is the number of contacts on one day per user and date.

Aggr(SUM({$<event={'Contact'}>} 1), user, date)

This will return "1" for the "Contact" bar itself and null for every other bar, even the one on the same day. Using only "date" has the same result.

My wild guesses would be that the dimension (which includes the time) ruins the Aggr - function or that the virtual table has multiple rows and doesnt get matched to the date of the bar chart.

Thank you in Advance!

Re: Aggr in Gantt chart returns wrong data for dates

sunny_talwar — Thu, 16 Jun 2016 11:06:56 GMT

I would like to color every bar (change background of expression) red that has a contact on the same day.

Contact on same day as what? Same day as Purchase

Re: Aggr in Gantt chart returns wrong data for dates

swuehl — Thu, 16 Jun 2016 11:08:35 GMT

Try

Max(TOTAL<user,date> Aggr(SUM({$<event={'Contact'}>} 1), user, date) )

Use Aggregation Functions!

Aggr(NODISTINCT SUM({$<event={'Contact'}>} 1), user, date)

Pitfalls of the Aggr function

Re: Aggr in Gantt chart returns wrong data for dates

Thu, 16 Jun 2016 11:10:19 GMT

Exactly. Purchase on the 12th with a follow-up contact = red, purchase on the 13th without a contact = not red. The color itself is not that important, i know how to do that stuff as soon as the conditional works.

Re: Aggr in Gantt chart returns wrong data for dates

Thu, 16 Jun 2016 11:15:33 GMT

Thanks for your answer! I did try the Max(TOTAL) aggregation already but it doesnt change anything.

Adding the NODISTINCT magically worked for me though. Can you give an explaination why it didnt work before and why it works with nodistinct? Understanding the issue will surely benefit me in the future.

Re: Aggr in Gantt chart returns wrong data for dates

swuehl — Thu, 16 Jun 2016 13:01:57 GMT

That's shortly explained in the second blog post.

What you are probably seeing is an issue called grain mismatch.

You are creating an virtual table based on dimensions user and date using the aggr() function, and then trying to project these table lines to an outer chart with more granular dimensions (i.e. also Time).

The aggr() function by default will produce only one result per user and date and thus only project this value once to a combination of user, date, time. All other times with same user, date will show a NULL.