Qlik Community

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: 
Sharad02
Contributor
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.Capture.PNG

Labels (3)
1 Solution

Accepted Solutions
edwin
Master
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;

View solution in original post

6 Replies
edwin
Master
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?

Sharad02
Contributor
Contributor
Author


p_1.PNG

 

 

 

 

 

 

 

 

 

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

 

Capture.PNG

 

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

this assumes your user selects CalYearMonth and not Date

you expressions should also use CalYearMonth

Sharad02
Contributor
Contributor
Author

Hi,

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

 

 

edwin
Master
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