set analysis with max date

[#1]

I have a union table which gathers purchases and sales and inventory by Date into a single table. It is linked to the calendar table and inventory subset has the inventory for every single day.

I am creating a sheet with just information associated to inventory. I am doing this with the set analysis, however because I am using the Max date, it is picking up the data within the entire set. But what it really should do is get the max date from where ind='inventory'.

sum({<Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}, ind={'inventory'}, Datetable_Date= {"$(=Max(Datetable_Date))"}>}  ItemInvValue)

The last load of inventory was 2016-03-16, but it is picking up the date as 2016-04-17.

Here is the union table:

uniontable:

Load

Date(Date, 'YYYY-MM-DD') as LinkTable_Join,

Inventorykey as masterid,

warehouse,

warehousekey,

OwnedQty,

SalesPurchasePrice,

OwnedQty * SalesPurchasePrice AS ItemInvValue,

'inventory' as ind

FROM

'lib://QlikQVDs (ema)/salesdailyinventorystatus.qvd' (qvd)

where Inventorykey <> 'A000T8VB';

Concatenate (uniontable)

Load saleswarehouse as warehouse,

masterid,

Date(transdate, 'YYYY-MM-DD') as LinkTable_Join,

transtype as TransactionType,

saleorderno as orderno,

unitsalesprice,

salesqty as Qty,

ExtendedSalesPrice,

'sales' as ind

FROM

'lib://QlikQVDs (ema)/expendablessales.qvd' (qvd)

where masterid <> ('A000T8VB');

Concatenate (uniontable)

Load

purchasewarehouse as warehouse,

Inventorykey as masterid,

Date(TransactionDate,'YYYY-MM-DD') as LinkTable_Join,

TransactionType,

orderno,

UnitPurchasePrice,

PurchaseQty as Qty,

ExtendedCostPrice,

'purchases' as ind

FROM

'lib://QlikQVDs (ema)/expendablespurchases.qvd' (qvd)

where Inventorykey <> ('A000T8VB');

Help is needed!

[tresesco]

Try like:

sum({<Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}, ind={'inventory'}, Datetable_Date= {"$(=Date(Max({< ind={inventory}>}Datetable_Date)))"}>}  ItemInvValue)

[sunny_talwar]

You further might need to add formatting in case Date isn't getting evaluated to the same format as the formats need to match between the LHS and RHS

Sum({<Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}, ind={'inventory'}, Datetable_Date= {"$(=Date(Max({<ind={'inventory'}>}Datetable_Date), 'YYYY-MM-DD'))"}>} ItemInvValue)

Replace YYYY-MM-DD with whatever format the Datetable_Date is in to get a result here.

Best,

Sunny

[#4]

HI Sunny,

This works for most part. But If i Change the year to 2015 i.e current year is 2016, the value of this changes to 0.

I want this value to stay static for most part and only be able to filter if the warehouse or category is selected from the filter. I tried removing the year from there but that doesn't work.Similarly, I don't want the value to changed based on Year, Quater, Month filter rather only the warehouse and category.

Any idea as to how this can be achieved?

[sunny_talwar]

May be this:

Sum({<Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}, ind={'inventory'}, Datetable_Date= {"$(=Date(Max({<ind={'inventory'}, Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}>}Datetable_Date), 'YYYY-MM-DD'))"}>} ItemInvValue)