Set Analysis Operation

[Anonymous]

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

[sunny_talwar]

May be something like this:

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

with AsOf as your dimension

[swuehl]

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))

or

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

or

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

or

=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

[swuehl]

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

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

[Anonymous]

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.

[swuehl]

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)

[Anonymous]

Thanks for yours return but it doesn't work 😕

I tried this:

[swuehl]

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>