Using Dimension in Expression

Anonymous — Fri, 03 Oct 2014 12:06:53 GMT

Fellow Qlikview-ers,

I have several customers with Shipments per Day (SpD) averages for every PeriodYear. Now if I want to show only those customer that are ‘new’ this PeriodYear compared to another PeriodYear, I use:

Dimension:

RelationName

Expression:

=if(sum({$<PeriodYear={$(=min(PeriodYear))}>} SpD)=0,sum({$<PeriodYear={$(=max(PeriodYear))}>} SpD),0)

So far, so good, using this in a Straight Table (or Pivot for that matter) doesn’t provide any problems.

Now the problem situation: I want to create a graph that shows 2 ‘Categories’: Won and Lost. I make the following graph:

Dimension:

None

Expressions:

Won = if(sum({$<PeriodYear={$(=min(PeriodYear))}>} SpD)=0,sum({$<PeriodYear={$(=max(PeriodYear))}>} SpD),0)

Lost = if(sum({$<PeriodYear={$(=max(PeriodYear))}>} SpD)=0,sum({$<PeriodYear={$(=min(PeriodYear))}>} SpD)*-1,0)

This formula gives me 0’s only. I’m guessing it has to do with the fact that I don’t use the Relation Name as a Dimension (since the total SpD is not 0 for the entire period).

What I want Qlikview to do, is take into the sum only those Relation Names that have a 0 SpD value in the specified PeriodYear. That way, I would get a total ‘Won’ and a total ‘Lost’ bar in my graph (to create a sort of waterfall graph, like in Excel).

Does anyone know how to change my formula in a way that it gets me the results I want?

Thanks in advance!

Kind regards,

Stefan

Re: Using Dimension in Expression

rubenmarin — Fri, 03 Oct 2014 12:17:49 GMT

Maybe:

Won = Sum(Aggr(if(sum({$<PeriodYear={$(=min(PeriodYear))}>} SpD)=0,if(sum({$<PeriodYear={$(=max(PeriodYear))}>} SpD),1)), RelationName))

Lost = Sum(Aggr(if(sum({$<PeriodYear={$(=max(PeriodYear))}>} SpD)=0,If(sum({$<PeriodYear={$(=min(PeriodYear))}>} SpD),1)), RelationName))

Not tested.

Re: Using Dimension in Expression

Anonymous — Fri, 03 Oct 2014 12:55:46 GMT

Superb, works after a few minor tweaks! Thanks a lot!

topic Re: Using Dimension in Expression in QlikView

Using Dimension in Expression

Re: Using Dimension in Expression

Re: Using Dimension in Expression