topic Set Analysis in QlikView

Set Analysis

Fri, 03 Mar 2017 20:10:21 GMT

Guys, take a look at follow table:

Tab:

LOAD * Inline [

city, year, exams, surgeries

ny, 2014, 25, 8

nj, 2014, 62, 7

ny, 2015, 81, 9

nj, 2015, 76, 10

ny, 2016, 74,

nj, 2016, 90,

];

If I want the sum of last year's exams with records - 2016 - I can use this dinamic expression:

Sum({< year={$(=Max(year))} >} exams)

Now, the last year's surgeries (also with records) was 2015. The set analysis above wont work.

How can I solve this?

( To do year={2015} ain't the answer. )

vishsaggi — Fri, 03 Mar 2017 20:15:46 GMT

Try this?

Sum({< year={$(=Max(year)-1)} >} surgeries)

Fri, 03 Mar 2017 20:23:28 GMT

Hi!

nice try but....

when I click in 2015, the expression shows me 2014 result.

Another guy in another forum brought this

Sum({< ano={$(=Max({<cirurgias-={''}>}ano))} >} cirurgias)

It was what I needed

vishsaggi — Fri, 03 Mar 2017 20:41:04 GMT

Oh good. Did not understand if that is what you were looking for.

You may also try this?

= Sum({< Year = {$(=Max({< surgeries = {"*"} >}Year))}>}surgeries)