topic Re: How to visualize top contents in QlikView

How to visualize top contents

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

Hi the community,

I need to visualize the audience of several websites.

I have 2 tables :

audience:

LOAD

SITE as CLE_site,

DATE as AUD_Date,

VISITORS as AUD_Visitors

topContents:

LOAD

SITE as CLE_site,

DATE as TOP_Date,

TITLE as TOP_Title,

VISITORS as TOP_Visitors

For each site, i need to show the daily audience, and among the highest values, i have to show the titles of the best contents.

For each content, i have to remind the date, and the number of visitors.

I try to get something like that ( i knew to display the 5 or 6 contents wich are higher than the daily average).:

Do you thing it is possible to do that with qlikview ?

Thank you for your help.

Re: How to visualize top contents

sergio0592 — Sun, 03 Jun 2018 14:02:30 GMT

Hi,

I can't open your .qvw because i'am in PE but i have tried to make a liitle sample reproducing your issue.

I think dual() function can be useful in your case. In my exemple, i display Date and Rubrique only for the two days with the most important visitors number (04/04 and 11/04).

Re: How to visualize top contents

rwunderlich — Sun, 03 Jun 2018 15:27:06 GMT

This example contains some additional ideas about annotating charts including using Dual() as sergio0592‌ mentioned.

Qlikview Cookbook: Tutorial - Annotating Chart Data Points http://qlikviewcookbook.com/recipes/download-info/tutorial-annotating-chart-data-points/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to visualize top contents

marieclaire — Sun, 03 Jun 2018 19:28:23 GMT

Thank you Jean-Baptiste for your help.

I tried your expression as following :

dual(if(rank(TOTAL Sum(TOP_Visitors)) <=2, TOP_Title&chr(10)&TOP_Date)&chr(10)&num(Sum(TOP_Visitors),'# ##0'), Sum(AUD_Visitors))

It does not work. Perhaps i did not use the right metrics ?

Regards,

Re: How to visualize top contents

marieclaire — Sun, 03 Jun 2018 19:30:26 GMT

Thank you very much Rob.

I am going to study this example which seems very well done and explained.

Regards,

Re: How to visualize top contents

marieclaire — Sun, 03 Jun 2018 21:03:10 GMT

Hi Rob,

Thanks a lot !

I get this line chart using the expression

=concat(replace(TOP_Title&TOP_Visitors,' ',chr(10)),','&chr(10))

It is a good beginning, not far from the purpose :

I do not manage to keep only the 5 highest labels (max TOP_visitors, rank, aggr ) ?

And is it possible to rotate them to get oblique labels ? It would be easier to read.

Thanks !

Re: How to visualize top contents

marieclaire — Sun, 03 Jun 2018 21:05:38 GMT

This is what i get :

Anyway thank you for your help !

Re: How to visualize top contents

sergio0592 — Mon, 04 Jun 2018 06:57:28 GMT

Hi, see the app in the attached file. But i can't understand why for the second top value, i can't fetch title.

Re: How to visualize top contents

rwunderlich — Mon, 04 Jun 2018 16:36:42 GMT

Oblique labels on data points is not possible.

-Rob

Re: How to visualize top contents

marieclaire — Thu, 07 Jun 2018 03:47:14 GMT

Hi Jean-Baptiste,

As oblique labels on data points, is not possible, i will mix your solution with Rob's one.

Thanks a lot for your help !

Re: How to visualize top contents

Frank_Hartmann — Wed, 20 Jun 2018 18:57:48 GMT

Also the second sum is not correct. try this:

dual(if(rank (TOTAL  aggr(Sum(TOP_Visitors),TOP_Title))<=2,TOP_Date&chr(10)&only(TOP_Title)&chr(10)&aggr(Sum(TOP_Visitors),TOP_Title),''),aggr(Sum(TOP_Visitors),TOP_Title))

Re: How to visualize top contents

marieclaire — Mon, 25 Jun 2018 14:57:17 GMT

Hello Franck,

Thank you for your intervention.

I try to represent the global audience curve, and on 4 or 5 peaks, the title of the contents concerned.

for the moment, the solution that comes closest to my need is based on 2 expressions :

=sum(AUD_Visitors)

=Aggr(if(rank(sum(TOP_Visitors))<=4,@CLE_Date&chr(10)&only(TOP_Title)&chr(10)),TOP_Title)

In your example, the line chart is based on TOP_Visitors.

I still have to fix an issue : the above expression does not always display dates on data points.

Any idea ?

Thank you