4 Replies Latest reply: Apr 29, 2016 2:38 AM by Sunny Talwar RSS

    set analysis with max date

    ASma Erum

      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!

        • Re: set analysis with max date
          Tresesco B

          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
              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

                • Re: set analysis with max date
                  ASma Erum

                  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?