topic Re: Re: Re: Re: Dimension Values in Set Analysis-followup question in QlikView

Dimension Values in Set Analysis-followup question

jrdunson — Tue, 14 Oct 2014 14:08:27 GMT

Hello,

I have a follow-up question to post http://community.qlik.com/docs/DOC-1335

[ Note I'm adding here, because I there is no 'attach' option in discussion 'add comments' ]

Attached is a spreadsheet... showing my Qlikview straight table values... where I followed the guidance in this post, to filter by dimension values:

I tried to mimic:

if(Customer2='A',sum({<Customer1={'A'}>} Sales)

,if(Customer2='B',sum({<Customer1={'B'}>} Sales)

,if(Customer2='C',sum({<Customer1={'C'}>} Sales)

So, why doesn't the expression calculate for each row?

a) I can put a single expression in a column (C1 or D1):

if([Memories Beg CFY Status]='Lapsed Donor',count(DISTINCT{<[Memories Current Status]={"Lapsed Donor"}>} [Memories Id]))

if([Memories Beg CFY Status]='Lybunt',count(DISTINCT{<[Memories Current Status]={"Lybunt"}>} [Memories Id]))

...and the values show in C2 and D3

b) However, if I use a calculation (as in E1):

"concat(distinct '=if([Memories Beg CFY Status]=' & chr(39) & [Memories Beg CFY Status] & chr(39) & ',count(DISTINCT{<[Memories Current Status]={""' & [Memories Beg CFY Status] & '""}>} [Memories Id]', ',') & ')' & repeat(')',count(distinct [Memories Beg CFY Status])) & repeat(')',count(distinct [Memories Beg CFY Status])-1 "

and the expressions render in columns D2 .. D6:

if([Memories Beg CFY Status]='Lapsed Donor',count(DISTINCT{<[Memories Current Status]={"Lapsed Donor"}>} [Memories Id]))

if([Memories Beg CFY Status]='Lybunt',count(DISTINCT{<[Memories Current Status]={"Lybunt"}>} [Memories Id]))

if([Memories Beg CFY Status]='Lybunt - 1st Time',count(DISTINCT{<[Memories Current Status]={"Lybunt - 1st Time"}>} [Memories Id]))

if([Memories Beg CFY Status]='Non-Donor',count(DISTINCT{<[Memories Current Status]={"Non-Donor"}>} [Memories Id]))

if([Memories Beg CFY Status]='Sybunt',count(DISTINCT{<[Memories Current Status]={"Sybunt"}>} [Memories Id]))

Yet the values do not render... why not?

Notice that C1 and E2 are identical....as are D1 and E3,

Is this what is meant, by set-analysis can only render once??? Why?

Jarrell

Re: Dimension Values in Set Analysis-followup question

Wed, 15 Oct 2014 08:51:12 GMT

It looks like the formula rendered in the bottom right cell is malformed, the count( formula doesnt have the closing ")" parentheses.

Try attaching a simplified QVW file along with the raw data table so we can check if that's the problem, the XLS isn't very clear.

What people mean when they say "set expressions are only evaluated once for every chart".

Simply stated, they refer to the fact a set expression will not be re-evaluated for each calculated value in the rows of another column (dimension) in a chart. Hence the "if([another column]='value',set expression A, set expression B)" logic.

In other words if you have table:

ColA ColB

Val1 Val3

Val2 Val4

Then a set expression as follows cannot be used to make the value of ColB dependent on the value of ColA in the same row:

sum({1<ColA=p(ColA)>} field)

In other words, you can't use a set expression to take the value from another column in the same table row as a search parameter within the set expression.

Instead you need to use a formula such as:

if([ColA]='Val1',sum({1<FieldA={'Val1'}>} field),

if([ColA]='Val2',sum({1<FieldA={'Val2'}>} field)

,null()))

Re: Re: Dimension Values in Set Analysis-followup question

jrdunson — Wed, 15 Oct 2014 12:02:53 GMT

Jasper, thanks so much. I'm not sure I get this...

if([ColA]='Val1',sum({$<ColA={'Val1'}>} field),

if([ColA]='Val2',sum({$<ColA={'Val2'}>} field)

,null()))

Do you mean:

if([ColA]='Val1',sum({$<ColB={'Val1'}>} field),

if([ColA]='Val2',sum({$<ColB={'Val2'}>} field)

,null()))

Consider the following

IDNum	BegDate	EndDate
30	Red	Green
45	Green	Green
50	Green	Red
23	Yellow	Yellow
56	Red	Green
66	Red	Red
72	Green	Yellow
55	Yellow	Red
67	Green	Green
101	Yellow	Red

All I want to do is show the count of distinct IDs for BegDate and EndDate, in the same chart

	BegDate	EndDate
Green	4	4
Red	3	4
Yellow	3	2

And this is what I tried ...

For the first column: count(DISTINCT [IDNum])

For the second column: if([BegDate]='Red',count(DISTINCT{$<[EndDate]={"Red"}>} [IDNum]))

I can do this really easily in SQL, in Perl, etc... Perhaps build a LOAD script to do this... How do I do this in a Qlikview chart? That is, I want to keep the IDs tied to the data model...so if, other dimensions are selected, the IDs will be selected...

Jarrell

Re: Re: Re: Dimension Values in Set Analysis-followup question

Wed, 15 Oct 2014 12:19:24 GMT

Jarrell, check the example attached. Is that the solution you're looking for?

(In the T2 table creation scripts you should still add a LOAD DISTINCT IF NOT EXISTS logic to avoid duplicate rows to keep things nice and clean.)

In my example the ColA bit in the set expression should really refer to a data field, as set expressions work with data fields, not with column names in front-end tables. I edited my first reply to clarify this.

Re: Dimension Values in Set Analysis-followup question

jrdunson — Wed, 15 Oct 2014 12:34:56 GMT

Hey, thanks

could I just use a data island:

T2:

LOAD * INLINE [

Col1

Red,

Green,

Yellow

];

Re: Dimension Values in Set Analysis-followup question

Wed, 15 Oct 2014 12:36:46 GMT

Sure, if the list of colors is static that wouldn't be a problem.

Re: Re: Dimension Values in Set Analysis-followup question

Wed, 15 Oct 2014 12:44:14 GMT

By the way, if your data model allows it I'd handle this issue using load scripts rather than using the dimension aware set expression workaround.

Check the attachment to see what I mean.

Re: Re: Re: Re: Dimension Values in Set Analysis-followup question

jrdunson — Wed, 15 Oct 2014 15:44:13 GMT

Jasper,

Hey, thanks for your help... I was able to solve most of this... by 1) either modifying my formula (above) to:

concat(distinct 'if( [Memories Beg CFY Status]=' & chr(39) & [Memories Beg CFY Status] & chr(39) & ',count(DISTINCT{<[Memories Current Status]={"' & [Memories Beg CFY Status] & '"}>} [Memories Id])', ',') & repeat(')',count(distinct [Memories Beg CFY Status]))... which renders:

if( [Memories Beg CFY Status]='Lapsed Donor',count(DISTINCT{<[Memories Current Status]={"Lapsed Donor"}>} [Memories Id]),

if( [Memories Beg CFY Status]='Lybunt - 1st Time',count(DISTINCT{<[Memories Current Status]={"Lybunt - 1st Time"}>} [Memories Id]),

if( [Memories Beg CFY Status]='Lybunt',count(DISTINCT{<[Memories Current Status]={"Lybunt"}>} [Memories Id]),

if( [Memories Beg CFY Status]='Non-Donor',count(DISTINCT{<[Memories Current Status]={"Non-Donor"}>} [Memories Id]),

if( [Memories Beg CFY Status]='Sybunt',count(DISTINCT{<[Memories Current Status]={"Sybunt"}>} [Memories Id]))))))

OR by using a 'data-island' approach that you suggested

--- However, in either case, the formulas doesn't render values... whereas hard-coded expressions do. In other words,

if I use the formula above, no values show... if I use the hard-coded text (the blue above) then values do render...

Jarrell

Re: Dimension Values in Set Analysis-followup question

Wed, 15 Oct 2014 16:28:24 GMT

Did you just simply paste the purple text in the chart expression? Because that won't work. You need to use dollar sign expansion so that the rendered formula is actually evaluated in qlik.

To practice with this add two text objects to QV,

One with the formula: ='=tod'&'ay()'

This one will just show "=today()" in the text object.

Next, add a text object with the fomula: =$(=' =tod'&'ay() ')

Which will render: "15-10-2014" instead of just the formula. That's because the rendered formula is evaluated by $().

Re: Dimension Values in Set Analysis-followup question

jrdunson — Thu, 16 Oct 2014 11:28:05 GMT

Jasper, thanks, the following works:

=$(=concat(distinct

'if( [Memories Beg CFY Status]=' & chr(39) & [Memories Beg CFY Status] & chr(39) & ',count(DISTINCT{$<[Memories Current Status]={"' & [Memories Beg CFY Status] & '"}>} [Memories Id])',

',') & ',count(DISTINCT [Memories Id])' & repeat(')',count(distinct {$<[Memories Current Status]=-{"Current Donor"}>} [Memories Current Status])))