topic Re: Aggreate "virtual" table versus a real table in QlikView

Aggreate "virtual" table versus a real table

Fri, 24 Oct 2014 17:19:57 GMT

Hello everyone,

I have a question that hopefully you gys will be able to answer..

Background:

Product A has 3 stock units updates (Jan - 600 units, Sep - 500 units and Oct - 1000 units). In some analysis, I want to know the stock units for a selected calendar date. For each product I have a maximum of one entry per month. In case I have no register for a product in a month I should consider the previous stock for that product.

Comparation of Aggr clause with field is not wo... | Qlik Community

Now I have a new question:

If I do a table like this:

Dimention: Product

Expression: FirstSortedValue({ <Date={"<=$(=(max(Date)))"}>} Units, -Date)

I get a a table that matches my request.

Now, I want to use the total (sum of units) coming from that expression.

I tried:

Sum (aggr( FirstSortedValue({ <Date={"<=$(=(max(Date)))"}>} Units, -Date), Product))

However the "virtual" aggregate table does not return the stock in missing moths.

Can anyone explain this? Is there another way to get to the total (for all products) to my expression?

Any comments will be deeply appreciated!

Thank you in advance!

Re: Aggreate "virtual" table versus a real table

Gysbert_Wassenaar — Sat, 25 Oct 2014 17:56:44 GMT

Try adding the set modifier to the outer sum too:

=sum({1 <d={"<=$(=max(d))"}>} aggr(FirstSortedValue({1 <d={"<=$(=max(d))"}>} exp, -d),p))

Re: Aggreate "virtual" table versus a real table

Mon, 27 Oct 2014 13:16:47 GMT

Thank you for your answer Gysbert. That didn't solve my problem since the aggr funtions is already selecting wrong values (it is not giving me the dates if there is no stock value for that month).

However your answer made me realize I had no set analysis on SUM and apparently I need it not to restrict the date to the selected values of Year and Month, like this:

Sum ({<Year=,Month=>}aggr( FirstSortedValue({1 <Date={"<=$(=(max(Date)))"}>} Units, -Date), Product))