Month Wise Comparison

[Sharad02]

Hi,

I Am trying to Solve this Month wise comparison that I have share it  Below.

Like If we choose Jan we only compare with january sales , if we choose Feb then it compare with  Jan and feb both.

[edwin]

hi it is not clear what we are comparing and what the issue is.  maybe you can elaborate.  also is this the input data or the expected output?  what are the rules?

[Sharad02]

In Above, I want 'record No and Line Should be in the Form Of  given Below ,

I had used below script for first Case Suggest me How to get the required output in second Pic.

MaxYearMonthTable:
Load
Month(Max(date(Date,'DD/MM/YYYY'))) as Maxmonth,
Year(Max(date(Date,'DD/MM/YYYY'))) as MaxYear
Resident Fact_Table_1;

Let vMaxYear=peek('MaxYear',0,'MaxYearMonthTable');
Let vMaxMonth=peek('Maxmonth',0,'MaxYearMonthTable');

SpendmaxYearTable:
NoConcatenate
Load *
Resident Fact_Table_1
Where Year(Date(Date,'DD/MM/YYYY'))=$(vMaxYear);

Calender:
Load
RecNo() as Record_No,
Date(AddMonths(makedate($(vMaxYear),1,1),recNo()-1),'YYYYMM') as CalYearMonth
AutoGenerate 12 ;

Left Join
Load
Record_No as Line,
CalYearMonth as calYearMonth2
resident Calender;

inner join (SpendmaxYearTable)

Load
Num(CalYearMonth) as YearMonth,
Num(CalYearMonth) as YearMonth2
Resident Calender

[edwin · Accepted solution]

it sounds like you want a month to compare to year to month.  i would solve this by creating a bridge.  your fact will have your monthly data, the bridge will connect your calendar to the fact.  when the user selects a month in the calendar, all year to month records in the fact will be selectable.

assuming your date field in the FACT is Date, your calendar date field is CalYearMonth, your bridge will look like this:

Date	CalMonthYear
202201	202201
202201	202202
202202	202202
202201	202203
202202	202203
202203	202203

//this assumes you have a Fact and Calendar table with Date and CalYearMonth fields

//Date is YYYYMM format
noconcatenate
tmp: load distinct Date resident Fact;

//the following creates a cartesian join
inner join (tmp)
load Date as CalYearMonth resident tmp;

//the following builds the bridge
Bridge:
load * resident tmp where Date <= Month;

drop table tmp;

[edwin]

this assumes your user selects CalYearMonth and not Date

you expressions should also use CalYearMonth

[Sharad02]

Hi,

Can You Explain through any example Or small data which Contain dates?

[edwin]

try it on your script, just change Fact to what you have - Fact_Table_1

remove these lines and add the code i am suggesting:

MaxYearMonthTable:
Load
Month(Max(date(Date,'DD/MM/YYYY'))) as Maxmonth,
Year(Max(date(Date,'DD/MM/YYYY'))) as MaxYear
Resident Fact_Table_1;

Let vMaxYear=peek('MaxYear',0,'MaxYearMonthTable');
Let vMaxMonth=peek('Maxmonth',0,'MaxYearMonthTable');

SpendmaxYearTable:
NoConcatenate
Load *
Resident Fact_Table_1
Where Year(Date(Date,'DD/MM/YYYY'))=$(vMaxYear);

Left Join
Load
Record_No as Line,
CalYearMonth as calYearMonth2
resident Calender;

inner join (SpendmaxYearTable)

Load
Num(CalYearMonth) as YearMonth,
Num(CalYearMonth) as YearMonth2
Resident Calender