topic Re: Expression for 'Closing' Figures in QlikView

Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 10:00:44 GMT

Hi, I'm trying to create an expression that gives me the sum of stock from only the LAST date of the current selection...

A basic equivalent of the tables used are...

StockHistory:

Product ID

Date

Stock Level

Products:

Product ID

Category 1

Category 2

Category 3

Calendar:

Date

Week

Month

I need, for any selections that may occur, the SUM([Stock Level]) only for the last date in the selection or the dimension in a chart. For example we might want to see a table with dimensions [Week] and [Category 3] and we would want to see the sum of stock for each category on the last day of each week shown in the table.

Also, the dimensions are not set in the tables/charts themselves, they are dynamic based on user selections/variables.

Hope that's clear! Any help much appreciated!

Darren

Re: Expression for 'Closing' Figures

mato32188 — Tue, 02 Dec 2014 10:40:05 GMT

Hi Darren,

try to use Set Analysis in your Expression (sum {<Date = {'$(=max(Date))'}>} StockLevel)

Re: Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 10:47:13 GMT

Thanks.

This expression only gives the closing stock for the very last date in the dataset. When used in a straight table broken down by week for example, it only shows the last week.

Re: Expression for 'Closing' Figures

mato32188 — Tue, 02 Dec 2014 11:09:19 GMT

Ok, what about firstsortedvalue(stocklevel, - date)

Re: Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 11:54:30 GMT

Hello,

This brings back just one stock figure for one item (the last one). I need the sum of all items on the last day of the selection or dimension.

Thanks for your help

Darren

Re: Expression for 'Closing' Figures

Tue, 02 Dec 2014 12:15:10 GMT

Hi Darren,

I would look into creating some fields with the relevant point in time flags you require for your dataset.

Sounds like you need an [Is Week End] and [Is Month End] flag. Once you have those flags you can use them within you set analysis expression to only give you the sum of the required days.

Sum( {<[Is Week End]= {1}>} StockLevel)

would the only bring back data for the required days meaning you would get the correct figure in each of your week dimension values. Same applies for month

hope that helps

Joe

Re: Re: Expression for 'Closing' Figures

mato32188 — Tue, 02 Dec 2014 12:21:50 GMT

Re: Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 12:27:48 GMT

Thanks Joe,

I think this would be a bit awkward to implement, the user will use a dimension 'group' to choose how they trend the data (by date, week, month etc) so I won't know what the user is looking at to use the relevant parameter. I was ideally looking for one expression that works holistically. So far I've got to this...

FirstSortedValue(AGGR(SUM({$} [StockLevel]),[Date]),0-AGGR(MAX({$} [Date]),[Date]))

Which seems to work when breaking down the total stock by period, however, when add another dimension such as product category, the data is completely wrong.

Thanks for your help

Darren

Re: Expression for 'Closing' Figures

Tue, 02 Dec 2014 12:45:36 GMT

Totally get where you are coming from, rather than a single complex expression, I would maybe look to use multiple simple expressions which are conditionally calculated.

You can use GetCurrentField(groupname) to give you which dimension is currently active in the group and based on that conditionally show the required expression.

condition: GetCurrentField(groupname)='Week'

expression: Sum( {<[Is Week End]= {1}>} StockLevel)

condition: GetCurrentField(groupname)='Month'

expression: Sum( {<[Is Month End]= {1}>} StockLevel)

I think you will find performance better also

hope that helps

Joe

Re: Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 12:57:53 GMT

Thanks Martin,

I'm afraid I haven't been able to apply this expression to my data model.

I've also found when I extend the data in your example (attached) I don't get results? Any ideas?

Thanks again

Darren

Re: Expression for 'Closing' Figures

johnso2080 — Tue, 02 Dec 2014 13:31:25 GMT

Thanks Joe, it seems like I might have to go with this approach...

Re: Expression for 'Closing' Figures

Tue, 02 Dec 2014 13:34:01 GMT

no worries, glad to help

Re: Expression for 'Closing' Figures

johnso2080 — Thu, 04 Dec 2014 16:31:55 GMT

Hi, are there any other ideas on this one..? 😕

Thanks!