Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try like:
sum({<Year=, Month=, Quarter=, MonthName=, _CYTD_Flag={1}, ind={'inventory'}, Datetable_Date= {"$(=Date(Max({< ind={inventory}>}Datetable_Date)))"}>} ItemInvValue)
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
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?
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)