topic Re: Is this possible in Set Analysis in QlikView

Is this possible in Set Analysis

shane_spencer — Tue, 26 Sep 2017 09:41:54 GMT

Hi All - In the attached I'm trying to populate the Final Value for Box 519 with the Count of the FK_FINANCIALDATA2 of Box 538. The trouble is the Ref: field is a Dimension so when I use Set Analysis to do a count of Box 538 it only shows in the row associated with Box 538 not all rows / Box 519. Is it possible to do this in the chart or do I need to sort it out in the load?

Re: Is this possible in Set Analysis

YoussefBelloum — Tue, 26 Sep 2017 10:02:38 GMT

Hi Shane,

on the Final value expression try to add the ELSE part to set an expression there.

and for the last COUNT VAT expression, try to add an IF statement to be able to calculate the rest.

Let me know !

Re: Is this possible in Set Analysis

shane_spencer — Tue, 26 Sep 2017 10:12:43 GMT

Hi Youssef - I'm not sure that I follow what you're suggesting. By the way the Count of VAT on Sales column will be removed if/when I can get that figure of 641 in to Final Value for Box 519. It was just a way of braking down the formula.

Re: Is this possible in Set Analysis

YoussefBelloum — Tue, 26 Sep 2017 10:26:01 GMT

The expression you put is: Count( {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

So if the BREF <> it will be 0.

I do not know if I understand your need...

Re: Is this possible in Set Analysis

shane_spencer — Tue, 26 Sep 2017 10:30:58 GMT

Somehow I want to get the Total Count of 641 for the row Box 538 in the cell for Final Value for Box 519.

So essentially instead of 641 being only in Count of VAT for Sales for the row called Box 538 I want it to be in all rows. So it's not dependent on the dimension. If I can work out a formula for that I can then use it as part of the If statement in the Final Value column.

Re: Is this possible in Set Analysis

Frank_Hartmann — Tue, 26 Sep 2017 11:12:14 GMT

try this:

if((If(Map_BoxRef_clo2='Box 519'

,Count( {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

,if(Map_BoxRef_clo2=9999,'-',sum(Box_Value)) )=0)

,Count(TOTAL {$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

,sum(Box_Value))

or this:

If(BREF='Box 519'

,Count( TOTAL{$< BREF={'Box 538'} >} FK_FINANCIALDATA2)

,if(Map_BoxRef_clo2=9999,'-',sum(Box_Value)))

Re: Is this possible in Set Analysis

shane_spencer — Tue, 26 Sep 2017 11:56:39 GMT

Hi Frank - the second formula worked a treat!