topic Re: How to override the dimension selection in straight table ? in QlikView

How to override the dimension selection in straight table ?

Anonymous — Mon, 06 Aug 2012 11:54:00 GMT

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!

Re: How to override the dimension selection in straight table ?

swuehl — Mon, 06 Aug 2012 13:11:17 GMT

Try as expression in your table chart

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

Re: How to override the dimension selection in straight table ?

Anonymous — Mon, 06 Aug 2012 14:16:41 GMT

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

Re: How to override the dimension selection in straight table ?

swuehl — Mon, 06 Aug 2012 22:12:53 GMT

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

Re: How to override the dimension selection in straight table ?

Anonymous — Wed, 08 Aug 2012 09:11:32 GMT

Thanks,

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

Thanks for your good help and directions !

Pascal