Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mmarchese
Creator II
Creator II

Is it possible to test derived fields in set expressions?

Can I put a dimension derived with Aggr in the left side of a set expression?  It's not working for me right now, but I'm not sure why.

For each product, I have the current stock and list of orders (with dates).  I'm trying to get the date for each product when its stock will run out.

 

// This works fine.
Set vd_stockRemaining = Aggr(
    Sum(TOTAL <SKU> m_stock) -
    Rangesum(Above(Sum(m_orders), 0, RowNo())), 
SKU, ([Order Date], NUMERIC));

// This does not work.  It returns null for every SKU.
Set vd_starvationDate = Aggr(Date(
    FirstSortedValue({<$(vd_stockRemaining)={"<=0"}>}[Order Date], -$(vd_stockRemaining))
), SKU);

 

I'm using this instead for now:

// This works but seems hacky/overcomplicated.
Set vd_starvationDate = Aggr(If(Sum(m_stock) > Sum(m_orders), Null(), Date(
    FirstSortedValue([Order Date], If(
	     $(vd_stockRemaining) <= 0,
	    -$(vd_stockRemaining), 
	    1000000000000
	)
))), SKU);

 

Labels (3)
0 Replies