Variables and Set Analysis Question

Wed, 16 Jan 2013 07:43:42 GMT

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

Re: Variables and Set Analysis Question

jagannalla — Wed, 16 Jan 2013 08:12:16 GMT

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

topic Variables and Set Analysis Question in QlikView

Variables and Set Analysis Question

Re: Variables and Set Analysis Question