on 04-04-2016 1:03 PM
Dear Gurus,
Could someone please explain how to use "no filter" operator from Webi in BEx Query Designer?
Customer is comparing webi and Bex report and we are not able to find a solution for the difference.
We have a Formula which is working fine when there is account,product, classification and calendar day is in characteristics,
Out of stock formula = Inventory < Treshold
Account | Product | Classification | Calendar Day | Out of stock (Formula) |
---|---|---|---|---|
801 | 121 | A | 01.12.2015 | 0 |
801 | 121 | A | 02.12.2015 | 1 |
802 | 122 | B | 12.02.2015 | 0 |
804 | 123 | A | 25.12.2015 | 1 |
Now they are removing drill-down for account,product and calendar day in webi
Expected
Classification | Out of stock (Formula) |
---|---|
A | 2 |
B | 0 |
BEx Report output
Classification | Out of stock (Formula) |
---|---|
A | 0 |
B | 0 |
the formula they are using in WEBi is as below
Sum((if[inventory]<= nofilter([Treshold]) in ([Account];[Product]) Then 1 ForEach([Calendar Day];[Account];[Product]))
Also they have suggested to use context on Account,Product and Calendar Day.
Please help how this can be achieved.
Regards
Karthik Kurpad
Hi,
how did you achieve zero in bex? did you use if else statement?
(inventory< threshold ) * 1 + 0
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jyothi,
Thanks for response.
We have used IF ELSE there.
(Inventory < Threshold) * 1 + 0.
It is fine when all four characteristics are there , Problem is when other characteristics are removed and classification is retained.
Webi is not considering "Out Of Stock" for filter, We need to know how we can achieve this through Bex.
Regards
Karthik Kurpad
Hello Loed,
This did not work,
instead Out of Stock is showing Zero.
Before Exception Aggregartion
Account | Product | Classification | Calendar Day | Out of stock (Formula) | Inventory | Threshold |
---|---|---|---|---|---|---|
801 | 121 | A | 01.12.2015 | 0 | 10 | 7 |
801 | 121 | A | 02.12.2015 | 1 | 6 | 7 |
802 | 122 | B | 12.02.2015 | 0 | 15 | 10 |
804 | 123 | A | 25.12.2015 | 1 | 6 | 7 |
After Exception aggregation
Account | Product | Classification | Calendar Day | Out of stock (Formula) | Inventory | Threshold |
---|---|---|---|---|---|---|
801 | 121 | A | 01.12.2015 | 0 | 10 | 7 |
801 | 121 | A | 02.12.2015 | 0 | 6 | 7 |
802 | 122 | B | 12.02.2015 | 0 | 15 | 10 |
804 | 123 | A | 25.12.2015 | 0 | 6 | 7 |
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.