topic Re: Sum by Max Date in QlikView

Sum by Max Date

Wed, 28 May 2014 21:57:04 GMT

Hello,

I have a table that contains warehouse, week and Inventory Units.

I would like to build a table that will dispaly the inventory the warehouse and inventory for a last week.

Something Like that:

Raw data:

Wharehouse Date Inventory

A 1 500

A 2 450

B 1 300

B 2 250

Table:

Wharehouse Inventory

A 450

B 250

Please Help

Thank you

Re: Sum by Max Date

Anonymous — Thu, 29 May 2014 00:01:47 GMT

Hello, Denis.

I think the easiest way is to create a new dimension, LastDate, in the script and check the option "Suppress When Value Is NULL" in the table. The code would be something like this:

WharehouseInventory:
Load * Inline [
    Wharehouse, Date, Inventory
    A, '01/01/2014', 500
    A, '02/02/2014', 450
    B, '01/01/2014', 300
    B, '02/02/2014', 250
];

Left Join (WharehouseInventory) Load
  Wharehouse,
  Date(Max(Date)) as Date,
  Date(Max(Date)) as LastDate
Resident WharehouseInventory
Group by Wharehouse;

An example app is attached.

Cheers.

Re: Sum by Max Date

eduardo_sommer — Thu, 29 May 2014 00:41:43 GMT

Create a straight table with wharehouse as dimension and firstsortedvalue(Inventory, -Date) as the expression. Please note the minus sign in the Date field, since we want the last week.

Eduardo

Re: Sum by Max Date

jagan — Thu, 29 May 2014 02:04:37 GMT

Hi Denis,

Try like this

Data:

LOAD

If(RowNo() <> 1 AND Previous(WareHouse) <> WareHouse, 1, 0) AS MaxDateByWareshouse

FROM DataSource

ORDER BY WareHouse, Date;

Now do this in chart

Dimension : WareHouse

Expression : Sum({<MaxDateByWareshouse={1}>}Inventory)

Hope this helps you.

Regards,

Jagan.

Re: Sum by Max Date

Thu, 29 May 2014 02:35:20 GMT

Thank you Bruno,

It works, but I would like to present the table without showing the date.

Is it a way to do that?

Thank you

Re: Sum by Max Date

Thu, 29 May 2014 02:37:13 GMT

Hi Edoardo,

Thank you for your responce.

When I use the formula I am getting no results.

Should I add anything else to expression?

Thank you

Re: Re: Sum by Max Date

eduardo_sommer — Thu, 29 May 2014 02:47:14 GMT

Hi,

Look at the attached app. It's very easy

Eduardo

Re: Sum by Max Date

Thu, 29 May 2014 02:47:24 GMT

HI Bruno,

Please ignore my previous post.

I just need to hide the column in Presentation tab.

Thank you for your help.

Re: Sum by Max Date

Thu, 29 May 2014 03:11:32 GMT

Hi Eduardo,

It works if you have only one dimensions - wharehouse, but if the table has more fields - as product type, product color etc the formula does not work properly.

I am not familiar with 'firstsortedvalue' function, but if it is possible to add the group by to it it would help.

Thanks a lot

Re: Sum by Max Date

eduardo_sommer — Thu, 29 May 2014 03:25:58 GMT

Hi Denis

If you have more dimensions, besides Wharehouse, they will work as a 'group by'. If you want to get the inventory of a color, of a product, of certain type, just add these dimensions and the expression will take the last inventory of that color of that product of that product type.

The firstsortedvalue takes the first value in a group of records (be them in a loading script or in a chart- pivot or straight table, for example). In the case of the chart, the dimensions act as the group by in the load statement. When you want the last record, you put a minus sign in front of the order variable - kind of a lastsortedvalue. For this reason i put -Date in the function.

It looks too simple to be true, but it works very fine.

Eduardo

Re: Re: Sum by Max Date

Anonymous — Thu, 29 May 2014 11:07:24 GMT

You're welcome, Denis.

Even though you said to ignore your previous post, let me add this. I thought you'd want to display the date so I used the field LastDate. But you don't have to. You could create a flag, like this:

WharehouseInventory:  
Load * Inline [  
    Wharehouse, Date, Inventory  
    A, '01/01/2014', 500  
    A, '02/02/2014', 450  
    B, '01/01/2014', 300  
    B, '02/02/2014', 250  
];  
  
Left Join (WharehouseInventory) Load  
  Wharehouse,  
  Date(Max(Date)) as Date,  
  1 as FlagLastInventory  
Resident WharehouseInventory  
Group by Wharehouse;

Then use the flag in your expression:

Sum({$<FlagLastInventory = {1}>} Iventory)

Or you can create both fields and use each one where you think is best.

Bruno.

Re: Re: Sum by Max Date

Thu, 29 May 2014 15:02:15 GMT

Thank you Bruno