How to Create YTD,MTD,WTD

[#1]

Hi Experts,

I have to tables:

Shown below

Time:

LOAD

Time_Id,

     Day_Dt,

     Year_Id,

     Fiscal_Year_Name,

     Year_Name,

     Quarter_Id,

     Quarter_In_Year,

     Quarter_Name,

     Month_Id,

     Month_In_Year,

     Month_Name,

     Week_Id,

     Week_In_Year,

     Week_Name,

     Fiscal_Period,

     Week_Date,

     Day_Key,

     Day_In_Year,

     Day_Name,

     Day_In_Week_Id,

     Day_In_Week_Name,

     Day_is_Holiday,

     Day_Is_Weekend,

     Week_End_Date,

     [Day Name],

     StartOfCurrentYear,

     StartOfLastYear,

     StartOfCurrentYear_Fiscal,

     StartOfLastYear_Fiscal,

     StartOfCurrentMonth,

     StartOfLastYearMonth,

     StartOfCurrentWeek_Fiscal,

     StartOfLastYearWeek_Fiscal,

     StartOfCurrentPeriodDate_Fiscal,

     StartOfLastYearPeriodDate_Fiscal

FROM

(qvd);

Fact_Table:

LOAD

     Price_Code_Key,

  Item_Standard_Key,

  Day_Key,

  Bid_Number_Key,

  Company_Key,

  Division_Key,

  CustomerGroup_Key,

  Credit_Reason_Key,

  Item_Key,

  OriginatingCL_Key,

  Route_Key,

  Ship_To_Customer_Key,

  Sales_Ship_To_Customer_Key,

  Sales_Order_Number,

  Stop_Customer_Key,

  Territory_Key,

  Warehouse_Key,

  Vendor_Key,

  Vendor_Item_Number_Key,

  Buyer_Key,

  PriceBook_Heading_Key,

  PriceBook_Heading_Family_Key,

  Brand_Key,

  Chain_Key,

  UPC_Key,

  Item_Type_Key,

  Item_Class_Key,

  Customer_Type_Key,

  Service_Rep_Key,

  Bill_To_Customer_Key,

  State_Key,

  Sales_Rep_Key,

  Location_Key,

  NULL() AS Employee_Key,

  NULL() AS Selection_Zone_Key,

  NULL() AS Labor_Code,

  Bid_Allowances,

  Catch_Weight_Sales,

  Catch_Weight_CRN,

  Delivery_Count,

  Ext_Actual_Cost,

  Ext_Allowance_Amt,

  Ext_Cigarette_Adv_Allow_Discount,

  Ext_Class_Discount,

  Ext_Item_Allowance_Amt,

  Ext_Net_Sales,

  Ext_Net_Sales_CRN,

  Ext_Sales_Gross,

  Ext_Salesrep_Cost,

  Ext_Salesrep_Cost_Gross,

  Ext_Salesrep_Cost_Net,

  Ext_Ship_Weight,

  Ext_SRP_Amt,

  Ext_Tracked_Allowance_Amt_Passed,

  Invoice_Id,

  Invoice_LineNum,

  Invoice_Seq_1,

  Invoice_Seq_2,

  Order_Number,

  Process_Day_Key,

  Invoice_Day_Key,

  Qty_Ordered_Sales,

  Qty_Shipped,

  Qty_Shipped_CRN,

  SalesRep_Cost,

  CurrentMonthNetSales,

  LastMonthNetSales,

  WeeksFactSales,

  MonthsFactSales,

  YearsFactSales,

  YearsOfFactTable,

  Fee_Ratio,

  GM_BM,

  Ext_Distr_Fee_In_Price,

  Ext_Distr_Fee_Not_In_Price,

    "Pieces_Cases_Or_Units",

    "Broken_Units_Ordered_Only",

    "Broken_Units_Shipped_Only",

    "Cases_Ordered_Only",

    "Cases_Shipped_Only",

    Ext_Market_Cost_Buyer,

    General_Sales_Code1,

    FiscalYear as [Fiscal Year],

    FiscalPeriod as [Fiscal Period],

    FiscalWeek as [Fiscal Week Nbr],

    Trade_Show_Allowances,

    Detail_Actual_Cost,

    Tobacco_Tax_Diff,

    Item_Class_Type,

FROM QVDs\Fact_Sales.QVD (QVD);

By using thsi to table i want YTD,WTD,MTD for Ext_Net_Sales.

please help me on that

Thanks

Siva

[#2]

Hi SIva,

It totally depends on requirement and you how want to show that in the dashboard:

You can use simple expression like:  Sum(Ext_Net_Sales) in Text Box or Charts and give user three buttons as YTD,WTD and MTD.

Set the following variables in your script:

let vReferenceDate=num(today());

let vYTD='<=' & date($(vReferenceDate)) & '>=' & date(YearStart($(vReferenceDate)));

let vWTD= '<=' & date(WeekEnd($(vReferenceDate))) & '>=' & date(WeekStart($(vReferenceDate)));

let vMTD= '<=' & date($(vReferenceDate)) & '>=' & date(MonthStart($(vReferenceDate)));

In each button add action as 'Select In field'  Field: Day_Dt and in search string add respective variable (e.g =vYTD )

Hope it helps.

Regards,

Sakir

[#3]

Hi Ali,

It's not working can you plese share q !vw,i will attach my data.

[#4]

Hi Ali,

Buttons are not working properly

Can you please help me on that..

Thanks

Siav

[#5]

Hi Siva,

Please find the attached qvw file with an example.

Hope it helps.

Regards,

Sakir