YTD Sales analysis Calculation

[wossenhh]

Hello,

I was trying to show the sales analysis for Fiscal year that shows from Apr to Mar. When I select Customer, Year and Month the sum of sales shows correctly. However If I select just Year, the sales amount shows way over.

The Expression formula that shows in text object looks like below :

=NUM(Sum({<Fyear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

=NUM(sum({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,FMonth=>}(History_Shipments * Price_Price)),'$ #,##0', '.',',')

One thing to mention, this formulas gives me correct answers when I do it on straight table. This happens only on Text object as the requirement is to display on text object.

Can anybody please help?

Thnaks,

Wossen

[MK_QSL]

What is the format of Fyear?

Can you provide sample app?

[wossenhh]

Manish - this is how it looks on the script of my calendar:

Set vFM = 4 ;         // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)          as FMonth,           // Dual fiscal month

         Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

         YearToDate(PeriodDate,0,$(vFM)) as FYTDFlag,     

          *;

LOAD DayID,

    PeriodDate,

    Floor(PeriodDate) AS DateNum, //it will gives you one whole number to represent date YTD - Year To Date

    Year(PeriodDate) as Year,

    Date(PeriodDate) as Date,

    Month(PeriodDate) as Month,

    Date(monthstart(PeriodDate), 'MMM-YYYY') as MonthYear,

    Day(PeriodDate) as Day,

    Day(PeriodDate) as Week,

    num(year(PeriodDate))*100+num(month(PeriodDate)) as year_month_num,

    'Q' & ceil(month(PeriodDate)/3) as QTY,

    WeekID,

    WeekStartDate,

    WeekEndDate,

    MonthID,

    MonthStartDate,

    MonthEndDate,

    QuarterID,

    QuarterStartDate,

    QuarterEndDate;

Fyear shows as 2013/2014

[vinieme12]

which year are your making the selection on Year or FYear

Why don't you consistently use the Numerical Fiscal year field which is 'fYear' in both expressions?

[wossenhh]

I am using fYear for Year which shows as 2014, 2015, and FMonth for the month, Apr, May, June...and so on.

It is good advise and I just changed it, but still I don't see any change.

[vinieme12]

can you upload a sample app

Preparing examples for Upload - Reduction and Data Scrambling

[wossenhh]

Here you go.

[MK_QSL]

Why you are using fYear in set expression?

Your Fiscal Year is from April to March so better to use FYear.

[vinieme12]

Hi Wossen,

The Expression is correct. I just downloaded the data in Excel and verified the same.

The reason Figures don't match when you select all customer's is that in your Data there are a lot of rows which have null() values in "Customer Name" and null values don't show up in list box.

I suggest During load add a validation and add default value for nulls in Customer Name field.

example

if(isnull([Customer Name],"NameNotFound",[Customer Name])  as [Customer Name],

so you can filter for those customers as well.

[wossenhh]

Hi Vineeth,

Thanks for looking into this. Yes, there are null values and that is why I have this  'WHERE Len( "Customer Name" ) > 0' on the script under the customer table.

But why I don't see the total YTD sales value on the text object where as when I use the same formula on straight table the amount looks correct.

Any clues?

Thanks,

Wossen