Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to override the dimension selection in straight table ?

Here is a problem I couldn't solve so far, (although I think it is a quite common one) :

* in my data I have ItemsProduced datalines, with a ProductionDate

* I also have ItemsSold datalines, also with SalesDate.

At any time the Stock = ItemsProduced - ItemsSold.

I can get this easily in a straight table, with (eg) month as a dimension.

This shows for each month the Production, Sales, as well as the movement of the Stock, ie. growing or shrinking.

I can even accumulate that, so it shows the total Stock, not just the movement for that month.

The problem however is, that when you select a time window (a year, month, ..) the accumulation always starts from 0

In fact it should start from the value it had by accumulating everything before the selection window.

I can calculate the value (eg in a text box) of Stock before the selection window :

= Sum ({$<year=, month=, datumNum={"< $(vStart)"} >} ItemsProduced)

- Sum ({$<year=, month=, datumNum={"< $(vStart)"} >} ItemsSold)

This tells qlikview to ignore the selection in year and month, and rather calculate over everything before 'vStart'. vStart is a variable defined as

=min(datumNum)

a similar trick is done with vEnd...

Problem is : I can't get this work inside the straight table... I'd like to show the actual stock in a selected time window, so adding the stock that was built up before the selected time window to the additional stock movement in the months of the selection time window..

Your help is appreciated!

4 Replies
swuehl
MVP
MVP

Try as expression in your table chart

=aggr(rangesum(above(total sum({1}ItemsProduced) - sum({1}ItemsSold),0,RowNo(TOTAL))),year,month)

Anonymous
Not applicable
Author

Thanks, it seems to calculate the stock correctly.

My next question is how does this work ?, as I don't like to use stuff that I don't understand myself...

I can do some study myself, but a few hints in the right direction could save me some time

swuehl
MVP
MVP

There are three things to know here

a) you can calculate a full accumulation like

=rangesum(above( sum(ItemsProduced) - sum(ItemsSold),0,RowNo()))

Please check the rangesum / above functions in the Help for more detail.

{ this is a variant, also often used: =rangesum(sum(ItemsProduced) - sum(ItemsSold),above(Stock2)) }

b) We want to look over year bounderies, so we need a table with year and month. You can create this table in place with advanced aggregation /  aggr() function and the TOTAL qualifier:

=aggr(rangesum(above(TOTAL sum(ItemsProduced) - sum(ItemsSold),0,RowNo(TOTAL))),year,month)

c) Now we would have a correct accumulation with no selections made, but we want to disregard selections in year for the accumulation:

=aggr(rangesum(above(total sum({1} ItemsProduced) - sum({1}ItemsSold),0,RowNo(TOTAL))),year,month)

if you need to consider selections in other fields, you can use a set expression that clears only selections in year / month:

=aggr(rangesum(above(total sum({<year, month>}ItemsProduced)

- sum({<year, month>}ItemsSold),0,RowNo(TOTAL))),year,month)

the results of the aggr() function will automatically be reduced to the outer tables dimensions (regarding also selections in these dimension fields).

Regards,

Stefan


Anonymous
Not applicable
Author

Thanks,

I figured out that indeed rangesum() and above() is the right place to start.

Thanks for your good help and directions !

Pascal