Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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