topic Re: Set Analysis Operation in QlikView

Set Analysis Operation

Anonymous — Tue, 29 Mar 2016 16:06:09 GMT

Hello,

I am an beginner on QlikView, and I would return the result (here Valu) of the year selected (here named AsOf) minus the year before (AsOf -1) in a Pivot Table. I tried this, but it returns all the result of all periods:

Do you have any advice?

Thank you in advance for your help,

Cocalero

Re: Set Analysis Operation

sunny_talwar — Tue, 29 Mar 2016 16:36:14 GMT

May be something like this:

Sum(Valu) - Above(Sum(Valu))

with AsOf as your dimension

Re: Set Analysis Operation

swuehl — Tue, 29 Mar 2016 16:48:11 GMT

Yes, maybe chart inter record functions can help you here.

But in a pivot table, and depending on your dimension sort order, you might need to use

=Sum(Valu) - Before(Sum(Valu))

=Sum(Valu) - After(Sum(Valu))

=Sum(Valu) - Above(Sum(Valu))

=Sum(Valu) - Below(Sum(Valu))

Also, with multiple dimensions, AsOf needs to be the inner most horizontal or vertical dimension.

If this is not helping you, please post some more details, like your current chart dimensions and expressions used.

Regards,

Stefan

Re: Set Analysis Operation

swuehl — Tue, 29 Mar 2016 16:49:55 GMT

And reading that you have selected a value, all this might not really help you, but maybe

Sum({<AsOf = {'$(=Max(AsOf)-1)'}>} Value)

Re: Set Analysis Operation

Anonymous — Wed, 30 Mar 2016 08:05:41 GMT

Thanks for your return swuehl, but it doesn't work :/.

Here my table of AsOf (Always one selected):

I would like a Pivot table which returns the result (Valu) between 2 months (AsOf - (AsOf -1))

The Expression that i tried:

Any idea please ?

Thanks for advance of your help,

Cocalero.

Re: Set Analysis Operation

swuehl — Wed, 30 Mar 2016 11:13:02 GMT

You were talking about a year selected in your OP, right? This list box shows dates, so your set expression needs to look like

Sum( {<AsOf = {$(=Date(AddMonths(AsOf,-1),'DD/MM/YYYY'))} >} Value)

Re: Set Analysis Operation

Anonymous — Wed, 30 Mar 2016 12:43:53 GMT

Thanks for yours return but it doesn't work 😕

I tried this:

Re: Set Analysis Operation

swuehl — Wed, 30 Mar 2016 12:55:13 GMT

And 'doesn't work' means exactly what? No result, wrong result, or...?

Have you checked that the dollar sign expansion returns the correct date?

And if you are using AsOf as dimension in your table, you probably need to use the total qualifier to get your expression result in a cell that refers to a current AsOf value:

Sum(TOTAL {<AsOf = {$(=Date(AddMonths(AsOf,-1),'DD/MM/YYYY'))} >} Value)

If you are using additional fields (and also AsOf field) as dimensions, you need to state these fields in the TOTAL field list:

TOTAL<Dim1>