sales aggregation from multiple excel files

[#1]

Hi Kush141087‌, sunindia‌,

I have a requirement in my project. It would be really great if you could help me with this. I have multiple files and each file contains sales information for three products A, B, C. And I need to aggregate the sales information monthly, quarterly and annually based on month selection which a user would do, for that UI should have a month list box.

The sales should be shown in a straight table.

Say when a user selects Feb 2015 the sales information for "Feb" month from 2015Feb file should be pulled for MTD but for YTD and QTD the info for Jan should come from 2015Jan file and info for Feb month should come from Feb2015 file.

Say if a user selects May 2015,

Then for MTD the sales data for May should come from 2015May file.

But for QTD, the April data should come from 2015April file and May data from 2015May file.

For YTD:

Jan data from 2015Jan file.

Feb data from 2015Feb file.

Mar data from 2015Mar file.

Apr data from 2015Apr file.

May data from 2015May file.

Hope this makes sense.

A sample code is greatly appreciated.

Thanks for all the help in the past and for this in advance. Your help means a lot.

Regards,

Sachin

[jagan]

Hi,

Check this link for sample expressions  YTD,MTD & QTD

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Calendar with flags making set analysis so very simple

[sunny_talwar · Accepted solution]

Script:

Table:

LOAD Metric,

    Region,

    FileName() as File,

    [42005],

    [42036],

    [42064],

    [42095],

    [42125],

    [42156],

    [42186],

    [42217],

    [42248],

    [42278],

    [42309],

    [42339]

FROM

[*.xlsx]

(ooxml, embedded labels, table is Data);

NewTable:

CrossTable (Date, Value, 3)

LOAD *

Resident Table;

DROP Table Table;

FinalTable:

LOAD *,

  If(File_MonthYear = MonthYear, 1, 0) as Flag;

LOAD Metric,

  Region,

  Date(MonthStart(Date#(Left(File, 7), 'YYYYMMM')), 'MMM YYYY') AS File_MonthYear,

  Date(MonthStart(Num#(Date, '##')), 'MMM YYYY') as MonthYear,

  Value

Resident NewTable;

DROP Table NewTable;

Straight table

Dimension: Metric

Expressions

MTD: Sum({<Flag = {1}, File_MonthYear = {"$(=MonthName(Max(File_MonthYear)))"}>}Value)

QTD: Sum({<Flag = {1}, File_MonthYear = {"$(='>=' & MonthName(QuarterStart(Max(File_MonthYear))) & '<=' & MonthName(Max(File_MonthYear)))"}>}Value)

YTD: Sum({<Flag = {1}, File_MonthYear = {"$(='>=' & MonthName(YearStart(Max(File_MonthYear))) & '<=' & MonthName(Max(File_MonthYear)))"}>}Value)

Numbers for YTD are not matching with May selection, are you sure the number in the images are absolutely correct?