Set Analysis - How to still show all values witouth selections

[stevegimbrollmt]

Hi peeps,

Wanted to check with you if this would be the best option when lets say, I have a Set Analysis on a field Sales.Period, and I would want to filter on that. However if there are no selections, I still want to show a value.  Usually if you do not select a the specific field, the sum will return Null.

Would this be the best way in doing so?

if(GetSelectedCount(Sales.Period)>0,

Sum({<Sales.Period = {$(=only(Sales.Period-1))} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat),

Sum(Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

)

Thanks a lot

Steve

[swuehl]

only() function will only return a value if there is one possible value, so if you make more than one selection, this will not work.

Then,

only(Sales.Period-1) is probably not correct, I assume you mean only(Sales.Period)-1

What do you want to achieve here? Maybe try this:

if(GetSelectedCount(Sales.Period)=1,

Sum({<Sales.Period = {$(=only(Sales.Period)-1)} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat),

Sum(Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

)

So if you select single Sales.Period, like 100, the dollar sign expansion should return 99:

if(GetSelectedCount(Sales.Period)=1,

Sum({<Sales.Period = {99} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat),

Sum(Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

)

If you select more than one value or none, the ELSE branch should be executed:

Sum(Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

You said, this returns Null, is this intendend? If not, fix this issue first before starting with the set modifier.

[stevegimbrollmt]

Hi Thanks for the feedback,

Let me try to explain it again and be a bit more clear.

So Lets say you have this situation:

a Period Selection Box

a Straight Table which has one expression to show current Period -- Sale (Sum({<Sales.Period = {$(=only(Sales.Period))} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat))

The other expression shows the previous Period -- Sum({<Sales.Period = {$(=only(Sales.Period)-1)} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

Now, if select no Period , the Straight table will return Null.  Once you select a Period, the expressions are calculated and data is displayed.

Is there a way when you select NO Period in the List Box, to still have the Sale displayed?

Thanks and hope I was a bit more clear

Steve

[tresesco · Accepted solution]

Try like:

for current period:

=Sum(Sales.Item_Qty*Sales.Item_Price_Inc_Vat)    // if I understood right, you want data according to selection, therefore without set this expression should work.

for previous period:

Sum({<Sales.Period = {$(=Max(Sales.Period)-1)} >} Sales.Item_Qty*Sales.Item_Price_Inc_Vat)

Note, Used Max() function instead of Only().

[stevegimbrollmt]

As you guys said, the Max() Did the trick, 

So when you have the Max(), it is basically saying that it will Display the Prev Max Period..

Thanks