Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vvvvvvizard
Not applicable

How add set analysis to this expression

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

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: How add set analysis to this expression

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

4 Replies
jontydkpi
Not applicable

Re: How add set analysis to this expression

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vvvvvvizard
Not applicable

Re: How add set analysis to this expression

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 .

sunny_talwar
Not applicable

Re: How add set analysis to this expression

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

jontydkpi
Not applicable

Re: How add set analysis to this expression

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein