Making a variable fit into range

[debmsarkar123]

Hi,

I have a variable-

$(Stockage)= 

Round(if(len(Batch)<2,1,IF(NOT ISNULL([Production Date]),if([Production Date]>Date,0, (Date - [Production Date])) /30, 10000) ))

Now In my chart expression , I want to display only stockage whose value is between 6 and 10000.

How can we do that?

[stevedark]

Hi @debmsarkar123 

The simple answer is that you can stick it in an IF statement:

=if($(Stockage) >= 6 and $(Stockage) <= 10000, $(Stockage), null())

But... there are a few things that are ringing alarm bells.

First of all, you are rounding to the nearest  10000, so any value returned by that variable will be a multiple of 10,000 - is that your intention?

The other thing is that there is no aggregation function in that statement, which makes me thing that the expression should probably belong in the load script, rather than a chart function, perhaps:

Round(if(len(Batch)<2,1,IF(NOT ISNULL([Production Date]),if([Production Date]>Date,0, (Date - [Production Date])) /30, 10000) )) as Stockage,

And then the chart expression might be something like sum(Stockage).

If that is the case then you would want to put the IF statement into the load script, perhaps utilising the varable you have:

$(Stockage) as Stockage,
if($(Stockage) >= 6 and $(Stockage) <= 10000, $(Stockage), null()) as [Stockage 6 to 10000],

In the chart you can then use whichever field you wish in a sum statement.

Hope that makes sense?

Steve

[debmsarkar123]

Hi Steve,

Thanks for the reply. 

I had a doubt while using-

if (sum([Stock Age])>=6 and sum([Stock Age])<10000,sum([Stock Age]))

it is producing blank values in other dimensions, I tried suppressing missing but its not working,

[stevedark]

Hi @debmsarkar123 ,

Sorry, I hadn't fully appreciated what the StockAge calculation was doing.

You could (but shouldn't) put the if statement in the stock part also:

if (max([Stock Age])>=6 and max([Stock Age])<10000,sum(Stock))

However, what you should really do is to create a field in the load script, called Stock Age which can be used as a dimension:

LOAD
   Batch,
   [Production Date],
   Date,
   Round(if(len(Batch)<2,1,IF(NOT ISNULL([Production Date]),if([Production Date]>Date,0, (Date - [Production Date])) /30, 10000) )) as [Stock Age],
   etc...

You can then simply have stock age as a dimension in your table, rather than a measure.

The stock calculation will then be where the IF resides:

sum(if([Stock Age] >= 6 and [Stock Age] < 10000, Stock, null())

Or, you can use the more efficient Set Analysis syntax of:

sum({<[Stock Age]={">=6<10000"}>}Stock)

You will find much written on Community about how Set Analysis worrks.

This should sort you out.

You should always try and do as many of the calculations as possible in the load script and as little as possible in the front end. You could even calculate a field called Stock Over 6 Days in the load script, making things even simpler in the table.

Hope that helps.

Steve