Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
johnso2080
Contributor
Contributor

Expression for 'Closing' Figures

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

12 Replies
mato32188
Specialist
Specialist

Hi Darren,

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

BR

M

ECG line chart is the most important visualization in your life.
johnso2080
Contributor
Contributor
Author

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.

mato32188
Specialist
Specialist

Ok,  what about firstsortedvalue(stocklevel, - date)

ECG line chart is the most important visualization in your life.
johnso2080
Contributor
Contributor
Author

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

Not applicable

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

mato32188
Specialist
Specialist

.

ECG line chart is the most important visualization in your life.
johnso2080
Contributor
Contributor
Author

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

Not applicable

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

johnso2080
Contributor
Contributor
Author

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