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.

siepe1990
New Contributor III

Using Dimension in Expression

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

1 Solution

Accepted Solutions

Re: Using Dimension in Expression

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.

2 Replies

Re: Using Dimension in Expression

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.

siepe1990
New Contributor III

Re: Using Dimension in Expression

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