Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Variables and Set Analysis Question

Hello,

I have what I imagine is a pretty simple question, but I am not having any luck putting this together.  I have the start of a very basic application, with a Date table and a Sales table, as shown below;

Date:

LOAD DateId,

    DateShort,

    FiscalWeekId,

    FiscalMonthId,

    FiscalQuarterId,

    FiscalHalfId,

    FiscalYearId,

SQL SELECT DateId,

    DateShort,

    FiscalWeekId,

    FiscalMonthId,

    FiscalQuarterId,

    FiscalHalfId,

    FiscalYearId,

FROM EDW.Logical."vEDW_D_Date_v2";

Facts:

LOAD

          DateId,

    LocationNum,

    TimeId,

    TranNum,

    Quantity,

    ExtGrossDollars,

    ExtDiscountDollars,

    ExtNetDollars,

    ExtCostDollars,

    Margin,

    ExtNetDollars_POS,

    ExtNetDollars_Concierge;

SQL SELECT

          DateId,

    LocationNum,

    TimeId,

    TranNum,

    Quantity,

    ExtGrossDollars,

    ExtDiscountDollars,

    ExtNetDollars,

    ExtCostDollars,

    Margin

FROM EDW.Logical.vEDW_R_Sales_v2

inner join

          EDW.Logical.vEDW_D_Location_v2

on  vEDW_D_Location_v2.Location_Key_As_Was          = vEDW_R_Sales_v2.Location_Key_As_Was

where DateId >= 20100131

I can create a chart, for example, aggregating to Date/Location and after adding list boxes for various fiscal perios (Month, Qtr, etc.) I can use these to filter the chart.

However, what if I want set up a chart which would always show sales for only the most recent day of sales loaded, how would I do this?  I imagine it's a combination of variables and set analysis, but I have not been able to get anything to work.

Thanks!

Chris

1 Reply
jagannalla
Valued Contributor III

Re: Variables and Set Analysis Question

Hi,

LOAD * INLINE [

    Product, Date, Sales

    A, 01-14-2013, 100

    B, 01-15-2013, 200

    C, 01-16-2013, 300

];

Take dim as Product and exp as =Sum({<Date={$(>=Date(Max(Date),'MM-DD-YYYY')-1)}>}Sales)  -> Last todays data

Cheers!!

Jagan

Community Browser