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

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

jmclaren — Sun, 15 Feb 2015 16:04:45 GMT

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

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

maxgro — Sun, 15 Feb 2015 17:32:20 GMT

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

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

jmclaren — Mon, 16 Feb 2015 08:42:28 GMT

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

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

jmclaren — Wed, 18 Feb 2015 22:49:57 GMT

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

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

rwunderlich — Thu, 19 Feb 2015 00:11:48 GMT

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

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

jmclaren — Thu, 19 Feb 2015 11:29:39 GMT

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