Can't select chart expression values - move to script?

[jmclaren]

Hi,

I have a qlikview chart (see attached) that summarises the total number of events undertaken by members - including zero. Unfortunately, if I try and filter, say, all the members who have done zero events in 2015 by clicking one of the zero's, the filter applied is for the single member record - not all zero records. I guess this is to do with the use of expressions in the chart. Would it be possible to add the totals to the member records in the main script, so they can easily be selected from a chart or table?

Thanks

Jim

[maxgro]

what about adding Searchable flag to the expression (arrow) and search (rectangle) for 0?

[jmclaren]

Hi Massimo

Yes, I can see that works...I would ideally like to leave users with one method for selection though. I intend producing bar charts etc with this data so would refer if the users could simply drag over the number of events to make their selections. Isn't it possible to calculate these four summary values and write them back as new dimensions to the member records all from within the script?

Thanks

Jim

[jmclaren]

Hi

Just hoping someone can suggest how I can summarise the events per member and add them back against the member records in the script. I imagine it should be fairly straight forward but being a new user I'm not sure how to do it...

Thanks

Jim

[rwunderlich]

Attached example qvw shows one way to create the Dimensions.

FIrst, create the "Total Events" field:

// Total Events

LEFT JOIN (Members)

LOAD

  MemberID

  ,count(EventDate) as [Total Events]

Resident Events

GROUP BY MemberID

;

If you also need a field for each EventYear:

// Create "Events in YYYY" fields

YearSummary:

Generic LOAD

  MemberID

  ,'Events in ' & EventYear

  ,count(EventDate) as EventCount

Resident Events

GROUP BY MemberID ,EventYear

;

The Generic statement will create a separate table for each year, which is ok because they are linked. But if you want merge the "Events in YYYY' fields into the Members table, add this bit of script.

FOR i = NoOfTables() to 1 STEP -1

  LET vTable = TableName($(i));

  IF '$(vTable)' LIKE 'YearSummary*' THEN

  LEFT JOIN (Members) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

-Rob

http://masterssummit.com

http://robwunderlich.com

[jmclaren]

Hi Robert

That works really well…and I can even understand how it works ☺. A key part of this requirement is to analyse low or non-participation and with this any years with no events appear as null – is there some way that the last script, which adds year totals back to the ‘Members’ table, can check for nulls and substitute ‘0’ instead? I've done some research and have tried using 'NULLASVALUE' but it's resulted in duplicate rows for thos items with nulls (see attached).

Many thanks

Jim