topic Re: Month Wise Comparison in App Development

Month Wise Comparison

Sharad02 — Fri, 05 Aug 2022 13:47:49 GMT

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.

Re: Month Wise Comparison

edwin — Fri, 05 Aug 2022 20:23:58 GMT

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?

Re: Month Wise Comparison

Sharad02 — Sat, 06 Aug 2022 11:26:18 GMT

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

Re: Month Wise Comparison

edwin — Sat, 06 Aug 2022 12:53:13 GMT

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;

Re: Month Wise Comparison

edwin — Sat, 06 Aug 2022 12:55:07 GMT

this assumes your user selects CalYearMonth and not Date

you expressions should also use CalYearMonth

Re: Month Wise Comparison

Sharad02 — Sat, 06 Aug 2022 16:21:45 GMT

Hi,

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

Re: Month Wise Comparison

edwin — Sun, 07 Aug 2022 13:54:31 GMT

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