Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
gumstero
New Contributor

Calculate outgoing balance for products with different max dates within a month

Hi,

I'm trying to calculate the outgoing balances for the chosen month for different products in the same pivot table.

The problem is that the products have different max dates.

Sum({$<%Date={'$(vMaxDate)'}>}DELIVERYQTY)

And the variable vMaxDate is: =Max({$<Type={'D'}>}%Date)

The products might have max dates:

Product A = Max date = 2016-02-29

Product B = Max date = 2016-02-27

One solution might be to create a variable that is a nested if or maybe in the script find the maxDate for each product.

vMaxDateA =Max({$<Product={'A'},Type={'D'}>}%Date)

vMaxDateB =Max({$<Product={'B'},Type={'D'}>}%Date)

and so on but the problem will be if new products arrive and to maintain the nested if :I.

Any ideas?

3 Replies
MVP & Luminary
MVP & Luminary

Re: Calculate outgoing balance for products with different max dates within a month

sum(aggr(FirstSortedValue(DELIVERYQTY, -%Date),Product))


talk is cheap, supply exceeds demand
gumstero
New Contributor

Re: Calculate outgoing balance for products with different max dates within a month

Thanks Gysbert,

I was actually just experimenting with firstsortedvalue and aggr.

I tried your expression but it returns 0 on all the rows.

I get some results with this, not the correct results though .

=FirstSortedValue( aggr(sum(DELIVERYQTY),Product,%Date), -aggr(%Date,Product,%Date) )

gumstero
New Contributor

Re: Calculate outgoing balance for products with different max dates within a month

Still haven't solved this, any ideas?

Gysbert, haven't got the expression with firstsortedvalue to work yet.