topic Re: Set Analysis Greater than variable from row in QlikView

Set Analysis Greater than variable from row

LucasD — Wed, 03 Mar 2021 21:50:30 GMT

Hello Everyone,

What I am trying to achieve seems very easy, but I am facing some troubles.

I Have basically 3 dimensions:

Phase.NUM= Is a number (yyyymm),
Ciclo = Cycle (number) (myyyy),

NewCiclo = Cycle as text (mm yyyy),

I am trying to get a table with the Sum of a "Total" only if my "Phase.NUM" is greater than my "NewCiclo", which need to be adjusted to the same format as Phase. Additionally, I have a filter applied on this page for Cycle Field that should be ignored for this table.

I could achieve partially with the code bellow, however, its not dynamically calculating based on every row of my table.

The part {">$(=Right(NewCiclo,4)&Left(NewCiclo,2))"} is bringing a fixed value following the filter selected not the "NewCiclo" value from each row.

Code:

SUM( {1}
aggr({1}

sum({$<[Phase.NUM]={">$(=Right(NewCiclo,4)&Left(NewCiclo,2))"}, Ciclo >}Total)

material, Ciclo

)
)

The expected result should be something like this:

Aggregating without "Phase.NUM" should get this:

Thanks in advance!

Re: Set Analysis Greater than variable from row

rwunderlich — Thu, 04 Mar 2021 04:29:32 GMT

You cannot create a set using row values. The set is calculated once for the chart. To do row by row processing you must use if(). In your case:

sum({$<[Ciclo= >}if([Phase.NUM] > Right(NewCiclo,4)& Left(NewCiclo,2), Total))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Set Analysis Greater than variable from row

LucasD — Thu, 04 Mar 2021 13:03:10 GMT

Thanks a lot, it worked!!!

Another tricky question... I need to do the same for the same row, based on a value from other row just like the table below.

I tried analogous with the previous solution, but no success.

(sum({$<Ciclo={'Actual'}>} if([Phase.NUM] >= Right(NewCiclo,4)& Left(NewCiclo,2), Total) + Sum({1} 0)))

What is getting complex, I guess, is that for this code the Phase.NUM parameter that I need is from rows which "NewCiclo = Actual" and the NewCiclo parameter is from the current row, I'm mixing up the things a little bit.

The idea afterwards is to achieve this:

Re: Set Analysis Greater than variable from row

rwunderlich — Thu, 04 Mar 2021 16:35:00 GMT

You'll need to use the TOTAL modifier to reference rows "outside your dimension"

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggregationFunctions/sum.htm

-Rob