Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Sum by Max Date


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

1 Solution

Accepted Solutions
bruno_montenegr
Contributor III

Re: Sum by Max Date

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.

11 Replies
bruno_montenegr
Contributor III

Re: Sum by Max Date

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.

Partner
Partner

Re: Sum by Max Date

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

MVP & Luminary
MVP & Luminary

Re: Sum by Max Date

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.

Not applicable

Re: Sum by Max Date

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

Not applicable

Re: Sum by Max Date

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

Partner
Partner

Re: Re: Sum by Max Date

Hi,

Look at the attached app. It's very easy

Eduardo

Not applicable

Re: Sum by Max Date

HI Bruno,

Please ignore my previous post.

I just need to hide the column in Presentation tab.

Thank you for your help.

Not applicable

Re: Sum by Max Date

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

Partner
Partner

Re: Sum by Max Date

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