Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
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
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;
this assumes your user selects CalYearMonth and not Date
you expressions should also use CalYearMonth
Hi,
Can You Explain through any example Or small data which Contain dates?
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