Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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().
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.
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
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().
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