Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tyember1
Contributor III
Contributor III

Calculated Dimension to filter for Value displayed instead of AGGR Dimension.

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




Labels (3)
1 Reply
prabhu0505
Specialist
Specialist

sample app if possible