Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a calculated dimension that categorizes customers into groups. The groups are formed by comparing sales $ from this current period to last year's period. I have the dimension working fine. But when filtered, it filters for the customers in that specific group. I have learned the cause is because the customers is what I AGGR on. Is there any way for the filter to only select the outcome of the calculated dimension? I have tried loading an inline table and having the formula pull from that instead, but I cannot get it to work. below is my working dimension:
=(aggr(
IF(round((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2019'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2019'}>}
(or_shipquant*or_price))))
= '0' AND
round((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2018'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2018'}>}
(or_shipquant*or_price))))
= '0',null(),
IF(round((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2019'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2019'}>}
(or_shipquant*or_price))))
=
round((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2018'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2018'}>}
(or_shipquant*or_price)))),'OK',
if((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2019'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2019'}>}
(or_shipquant*or_price)))
= '0', 'Gap',
if((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2018'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2018'}>}
(or_shipquant*or_price)))
= '0', 'Gain',
if((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2019'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2019'}>}
(or_shipquant*or_price)))
<
(Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2018'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2018'}>}
(or_shipquant*or_price))) , 'Decrease',
if((Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2019'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2019'}>}
(or_shipquant*or_price)))
>
(Sum({$<OrdStatus = {'Open'},[Fiscal Year] = {'2018'}>}
(or_ordquant*or_price))
+
Sum({$<OrdStatus = {'Invoiced','Shipped Non Invoiced'},[Fiscal Year] = {'2018'}>}
(or_shipquant*or_price))),
'Increase','OK')))))),bi_name))
Thanks
sample app if possible