topic Re: How add set analysis to this expression in QlikView

How add set analysis to this expression

vvvvvvizard — Tue, 19 Sep 2017 09:01:48 GMT

The expression below works correctly and displays the data for the selected year by the user correctly

if([FIN JAAR]=$(MAKS_JAAR), Sum(if(Aggr(Sum( TOTAL <PLU> BW),PLU)<0,Aggr(Sum(TOTAL<PLU> BW),PLU))))

How do i add another expression to show the same data as above for the previous year , i tried this but it doesnt work

Sum(if(Aggr(Sum({< [FIN JAAR]={$(y_previous_year)}> TOTAL <PLU> BW),PLU)<0,Aggr(Sum({<[FIN JAAR]={$(y_previous_year)}>} TOTAL <PLU> BW),PLU)))

also

if([FIN JAAR]=$(_previous_year)..... ect wont work , because the year the user selects is the maximim year , i think you have to add it into set analsysis for the previous year

Re: How add set analysis to this expression

jonathandienst — Tue, 19 Sep 2017 09:19:34 GMT

I suspect you are over-complicating the expression. Why do you need an Aggr()? I would try

For the current year:

=Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} TOTAL <PLU> BW)

For the previous year

=Sum({<[FIN JAAR]={$(=MAKS_JAAR - 1)}>} TOTAL <PLU> BW)

Assumes that MAK_JAAR is a variable containing a year (like 2017) and that the users are making a selection on FIN_JAAR (and the a selection in FIN_JAAR modifies the value of MAKS_JAAR).

Re: How add set analysis to this expression

vvvvvvizard — Tue, 19 Sep 2017 10:01:44 GMT

The reason for the Aggr() is that in the granular data people scan the barcode in the price column by mistake, and they cancel it out with a -price equal to the barcode = -1626236363563653 +1626236363563653 +300

so the 1626236363563653 pair is the illegitime numbers and the +300 is the right number ,sometimes there are legitimate negitive numbers .

I aggr the sum per invoice to avoid the illiegitimate pair , but know i only want to show legitimate negitive numbers .

Re: How add set analysis to this expression

sunny_talwar — Tue, 19 Sep 2017 12:09:00 GMT

May be this

If(Only({<[FIN JAAR] = {$(y_previous_year)}>} [FIN JAAR]) = $(_previous_year), Sum({<[FIN JAAR] = {$(y_previous_year)}>} If(Aggr(Sum({<[FIN JAAR] = {$(y_previous_year)}>} TOTAL <PLU> BW),PLU) < 0, Aggr(Sum({<[FIN JAAR] = {$(y_previous_year)}>} TOTAL <PLU> BW), PLU))))

Re: How add set analysis to this expression

jonathandienst — Tue, 19 Sep 2017 12:51:21 GMT

Its not clear to me how the Aggr() helps you. Simply summing the values would cause the invalid entry to be cancelled by its reversal. Perhaps it would be clearer if you posted a small sample qvw. It does not have to have real data, but the sample data should illustrate the structure of the real data.

Note that an Aggr() respects the current set and is affected by user selections and set expressions. If you want previous year's data you will need to set the year override on the outer sum (like Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} ... ) as well as the inner ones.Something along these lines

Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} Aggr(Sum({<[FIN JAAR]={$(MAKS_JAAR)}>} ... )....)