Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

set analysis with max date

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!

4 Replies
MVP
MVP

Re: set analysis with max date

Try like:

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

Re: set analysis with max date

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

Not applicable

Re: set analysis with max date

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?

Re: set analysis with max date

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)

Community Browser