topic Re: Expression =! Dimension in QlikView

Expression =! Dimension

unkisoN — Mon, 15 Mar 2021 15:18:28 GMT

Hi colleagues,

i have a simple pivot chart with a lot of dimensions.

I have two expressions

Weight (kg): = sum(totalweight)
Fulfillement = sum(totalweight)/Forecast

I had some problems with the Fulfillment so i decided to make the expression simple and make it just "=Forecast".

Now as you can see in 2015, there is only one line which shows the correct Forecast in 2015 with "n.a."

The rest is not shown correctly.

What should i do so that the Expression "Forecast" is the same as the Dimension "Forecast" ?

Re: Expression =! Dimension

rwunderlich — Mon, 15 Mar 2021 16:56:57 GMT

You likely have more than one Forecast value for this DImension(s), so you will need an aggregation.

Sum(Forecast)

-Rob

Re: Expression =! Dimension

unkisoN — Tue, 16 Mar 2021 07:40:07 GMT

Hi @rwunderlich

i tried this as well but the problem is then i have everywhere "0" and also the Text "n.a." should not be summed up and needs to be shown as text

Re: Expression =! Dimension

MayilVahanan — Tue, 16 Mar 2021 08:34:26 GMT

HI @unkisoN

Try like below

If(MaxString(Forecast) = 'n.a', MaxString(Forecast), Sum(Forecast))

If((Forecast) = 'n.a', (Forecast), Sum(Forecast))

Re: Expression =! Dimension

unkisoN — Tue, 16 Mar 2021 08:44:55 GMT

Hi @MayilVahanan

both did not work ...

I have tried now =aggr(Forecast, ItemCode)

Now i get better results for the columns "no data" but still there are some missing values...

Re: Expression =! Dimension

marcus_sommer — Tue, 16 Mar 2021 16:39:03 GMT

It seems that your datamodel isn't really suitable for your wanted view. It looked a bit like a cartesian structure with having a year-field as vertical dimension as well as horizontal dimension whereby not existing values became NULL.

You may try to overcome respectively to ignore the dimensional context with a TOTAL statement, like:

concat(TOTAL <Dim1, Dim2> Forecast, ' + ')

whereby Dim1 and Dim2 are just placeholder because you want surely not ignore every dimensionality else only certain ones.

If this didn't work like you expect it I suggest to rethink the entire approach - because that the chart displayed NULL respectively their replacement with '-' isn't wrong because there aren't any data.

- Marcus