YTD and MTD Counts

[#1]

I am having trouble parsing out the appropriate Counts in one of my charts. I have several years of data loaded in and I have two expressions I am trying to create. The first is a count of unique orders MTD and the second is a count of unique orders YTD. Unfortunately I do see how I can get the counts to show up in the same chart.

Any suggestions would be appreciated.

[Oleg_Troyansky · Accepted solution]

There are functions like InYearTodate, InMonthTodate, InQuarterTodate, that you can use to validate the condition on the fly:

InYearToDate ( date, basedate , shift [, first_month_of_year = 1] )

I prefer moving this logic back into the load script and generating flags in my Master Calendar that would already have 1 or 0 for each relevant condition (YTD, MTD, QTD, Rolling 12 months, etc...)

Using the flags, we can simplify our expressions, for example:

YTD Sales = sum( {$<YTDFlag={1}>} Sales)

MTD Sales = sum( {$<MTDFlag={1}>} Sales)

The second part of your question is how to count distinct orders. You can always use count(distinct OrderID), however, it's quite a slow and memory-consuming operation. We usually recommend preparing a "counter" field in the table that holds unique values - in your case, Order Header. If you load 1 for each order:

load

OrderID,

1 as OrderCount,

...

then your expression is as simple as summarizing the value of the OrderCounter:

YTD Orders = sum( {$<YTDFlag={1}>} OrderCounter)

YTD Orders = sum( {$<MTDFlag={1}>} OrderCounter)

cheers,

[#3]

That is very useful information, thank you. It actually gets me most of the way with my current chart. One little snag I have with applying that concept to another expression in my chart is calculating distinct stores for MTD and YTD sales. Orders and Sales is easy but then within that subset is a smaller group of unique stores. Since some of the stores can have multiple orders I am not sure how best to apply this concept to that scenario.

Any suggestions?

[Oleg_Troyansky]

If you have a separate table for stores, you can load 1 as a counter there. If you don't - you can create it, just for this purpose:

Stores:
load distinct
 StoreNumber,
 1 as StoreCounter
resident
 Orders
;

The rest is pretty much the same

[#5]

Awesome. That does the trick. Thanks so much.

[#6]

Ok, that actually didn't help unless I am doing something wrong. I created a table of unique stores and match it up to my Order Header. I use the MTDFlag to determine the appropriate matches but it still counts the store twice (whether summing the Count field or counting the storekey) if there are two orders with the same store. So right now I have a count of 11 but it should be 10.

What might I be doing wrong?

[#7]

Dear Oleg,

Could you explain me how u write Rolling Months in script? Below you can see my Calendar:

LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo()-1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
Week(TempDate) AS CalendarWeek,
Weekday(TempDate) AS WeekDay,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
YearToDate(TempDate, 0, Ceil(Month(Today())/3) * 3 - 2 )*-1 as CurQTDFlag,
YearToDate(TempDate, -1, Ceil(Month(Today())/3) * 3 - 2 )*-1 as LastQTDFlag,
Year2Date(TempDate, 0, Month(Today()))*-1 as CurMTDFlag,
Year2Date(TempDate, -1, Month(Today()))*-1 as LastMTDFlag

RESIDENT TempCalendar
ORDER BY TempDate ASC;

Thank you in advance,

Have a nice day,

Beata Jablonska