Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner
Partner

Set analysis - Get only max transaction date from each location

In an inventory app, i have the inventory balances per item per location but only per day where there was an actual inventory transaction on an item...

Meaning:

LocationItemDateQTY transactionQTY Balance
111th of dec 201612
114th of dec 201646
1110th of dec 2016-24
224th of apr 20171014
228th of may 2017-59

Ok... If I want to know what the inventory balance of item 1 and 2 was on for example the 15th of december 2017. Notice, that is the newest QTY balance per item per location I want... Meaning that Item 2 on location 2 has the balance of 9 on the 15th of december, since that is the latest balance I have... I might also want to know what the balance was on the 6th of may 2017...... The result :  14...

So... Can this be done only with set analysis?

I was thinking something like:  Date <= "selected date", but should be the max date per location per item...

How to?

Regards Anders.

1 Solution

Accepted Solutions
Highlighted
luismadriz
Valued Contributor

Re: Set analysis - Get only max transaction date from each location

Hi,

Interesting!

Something like this?

1Untitled.png

2Untitled.png

This is the one for the [Qty Balance]

=AGGR(FirstSortedValue({<Date={"<=$(=GetFieldSelections(DateSelection))"}>} [QTY Balance],-Date),Location,Item)

Maybe there are better ways of doing it,

I hope this helps,

Cheers,

Luis

View solution in original post

3 Replies
Highlighted
luismadriz
Valued Contributor

Re: Set analysis - Get only max transaction date from each location

Hi,

Interesting!

Something like this?

1Untitled.png

2Untitled.png

This is the one for the [Qty Balance]

=AGGR(FirstSortedValue({<Date={"<=$(=GetFieldSelections(DateSelection))"}>} [QTY Balance],-Date),Location,Item)

Maybe there are better ways of doing it,

I hope this helps,

Cheers,

Luis

View solution in original post

Highlighted
Partner
Partner

Re: Set analysis - Get only max transaction date from each location

Thanks... I got it working... But I needed a SUM around the AGGR, where I selected the date <= max(date)... in set analysis... so it ended up looking something like this:

SUM(({<InventoryDate={"<=$(=Max(Date))"}>}AGGR(FirstSortedValue({<InventoryDate={"<=$(=Max(Date))"}>} [QTY Balance],-InventoryDate),Location,Item))

Thanks a lot...

Wonder if a Rank funktion could have been used also....

Regards,

Anders

Highlighted
luismadriz
Valued Contributor

Re: Set analysis - Get only max transaction date from each location

Hi,

I think Rank would have given you the position according to something, then you would've needed to get the value (QTY Balance) based on that position. FirstSortedValue gets you the value based on that position.

But I'm sure there are other ways of doing the same. I think it's good to find other ways of doing especially if there are performance issues. With small databases you could get away with almost anything...

Cheers,

Luis