Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggreate "virtual" table versus a real table

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!

1 Solution

Accepted Solutions
Not applicable
Author

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))

View solution in original post

2 Replies
Gysbert_Wassenaar

Try adding the set modifier to the outer sum too:

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


talk is cheap, supply exceeds demand
Not applicable
Author

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))