Expression =! Dimension

[unkisoN]

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" ?

[rwunderlich]

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

Sum(Forecast)

-Rob

[unkisoN]

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

[MayilVahanan]

HI @unkisoN 

Try like below

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

or

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

[unkisoN]

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

[marcus_sommer]

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