Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vvvvvvizard
Partner - Specialist
Partner - Specialist

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

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

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
Partner - Specialist
Partner - Specialist
Author

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

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

jonathandienst
Partner - Champion III
Partner - Champion III

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