cancel
Showing results for
Did you mean:
Creator

Set analyse - blank values

Hello,

I have problem with set analyse. My set analyse is :

-1 * Sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"}, [Object] = {'whinr110'},

[ID cost division] ={'701', '702', '703'} >}[Bill out of])

Everything works, but I have also some accounting records, where "ID cost division" is empty and I need them in my choice.. How can I choose in Set analyse also records, where is ID cost division unwritten.

Thank you.

1 Solution

Accepted Solutions
MVP

May be create a flag in the script

If(Len(Trim([ID cost division])) = 0 or Match([ID cost division], 701, 702, 703), 1, 0) as Flag

....

and then this

-1 * Sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"}, [Object] = {'whinr110'},

Flag = {1}>}[Bill out of])

You can bring all the conditions into the script if you want...

2 Replies
MVP

May be create a flag in the script

If(Len(Trim([ID cost division])) = 0 or Match([ID cost division], 701, 702, 703), 1, 0) as Flag

....

and then this

-1 * Sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"}, [Object] = {'whinr110'},

Flag = {1}>}[Bill out of])

You can bring all the conditions into the script if you want...

Partner - Champion III

In general, I would create a flag as Sunny suggests. But of for some reason you prefer not to change the load script (or cannot), then you may be able to do this in the front end.

If "empty" means an empty string ('') then use:

-1 * Sum ({<

[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"},

[Object] = {'whinr110'},

[ID cost division] ={'701', '702', '703', ''}

>} [Bill out of])

But I suspect by "empty" you mean NULL. Then try like this

-1 * Sum ({

<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396", "501398", "501399", "501400", "501401", "501588"},

[Object] = {'whinr110'}>

* ((1 - <[ID cost division]= {'*'}>) + <[ID cost division]={'701', '702', '703'})>)

} [Bill out of])

The highlighted expression takes all possible value and deducts all he cost divisions that have content. It then adds the three explicit cost divisions.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser