# App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for
Search instead for
Did you mean:
Contributor

## Month Wise Comparison

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.

Labels (3)

• ### General Question

1 Solution

Accepted Solutions
Master

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;``````
6 Replies
Master

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?

Contributor
Author

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

Master

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;``````
Master

this assumes your user selects CalYearMonth and not Date

you expressions should also use CalYearMonth

Contributor
Author

Hi,

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

Master

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