# 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.

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?

Kind regards,

Stefan

Tags (3)
1 Solution

Accepted Solutions MVP

## 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 MVP

## 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.

New Contributor III

## Re: Using Dimension in Expression

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

Community Browser