topic Re: Using Concat Aggr in a Heatmap in QlikView

Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 14:37:57 GMT

Hi All,

I have the following expression

=Concat(Customer & ' - ' & Num(Aggr($(e.tickets), Customer), '#,##0'), chr(10), -Aggr($(e.tickets), Customer))

Which returns the correct number of tickets for each Customer, However when I add to a heatmap and hover over the day, it will not display the Qty's for each Customer, any ideas.

if I add the above expression in a text object it works fine

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 14:48:01 GMT

If you just use this as your expression... does it show you the numbers?

Num(Aggr($(e.tickets), Customer), '#,##0')

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 14:50:13 GMT

No i'm afraid not

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 14:51:04 GMT

What is behind this variable? $(e.tickets)

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 14:57:27 GMT

COUNT (DISTINCT Ticket_No)

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 14:59:01 GMT

Does this work?

Num(Aggr(COUNT (DISTINCT Ticket_No), Customer), '#,##0')

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 15:03:18 GMT

I will try it shortly, just running a reload on another document

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 15:14:39 GMT

Hi Sunny,

That does not work either, as I said, this works fine in a text object

=Concat(Customer & ' - ' & Num(Aggr($(e.tickets), Customer), '#,##0'), chr(10), -Aggr($(e.tickets), Customer))

but then fails on the heatmap

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 15:19:19 GMT

May be you need to add Year, WeekDay, Week in your Aggr() function....

=Concat(Customer & ' - ' & Num(Aggr($(e.tickets), Customer), '#,##0'), chr(10), -Aggr($(e.tickets), Customer, Year, WeekDay, Week))

If this doesn't help, would you be able to share a sample?

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 15:22:23 GMT

I tried that earlier today and it did not return the correct result.

But I will try again, if it does not work I will try share a reduced version of the App.

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 15:24:15 GMT

Sounds good

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 16:58:05 GMT

Please the attached file, still early stages, have decided on design yet

Re: Using Concat Aggr in a Heatmap

sunny_talwar — Thu, 04 Jan 2018 17:23:43 GMT

Try this

=REPEAT(CHR(13),2) & REPEAT('-',10) & chr(13) & $(e.tickets) & ' Tickets Created' & CHR(13) &

Concat(DISTINCT If(Len(Trim(Aggr($(e.tickets), Customer, Year, Weekday, Week))) > 0, Customer & ' - ') & Num(Aggr($(e.tickets), Customer, Year, Weekday, Week), '#,##0'), chr(10), -Aggr($(e.tickets), Customer, Year, Weekday, Week))

Re: Using Concat Aggr in a Heatmap

rustyfishbones — Thu, 04 Jan 2018 17:36:16 GMT

Perfect