Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using set analysis to compare end of month data by product

Currently I have end of month data for one full calendar year 2014 and 2015 data will feed automatically. What I want is to create a tables that compares my end of month numbers for a product to the previous month/previous quarter/previous semester/previous year. This way I can see how I am doing on a monthly/quarterly/semester/yearly basis. So ideally I would need 4 tables, one for month, one for quarter, one for semester data and one for yearly data.

I have created a master calendar within my script to show:

LOAD TempDate as Date,
Day(TempDate) as Day,
Week(TempDate) as Week,
Weekday(TempDate) as WeekDay,
Month(TempDate) as Month,
Year(TempDate) as Year,
'Q' &
ceil(Month(TempDate) / 3) as Quarter,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday)
, 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal
Order By TempDate ASC;

Now if I have the following data for example:

DateProductBalance
12/31/2014Tran$350,000
12/31/2014Time$150,000
12/31/2014Loan$100,000
11/28/2014Tran$400,000
11/28/2014Time$100,000
11/28/2014Loan$150,000
10/31/2014Tran$500,000
10/31/2014Time$400,000
10/31/2014Loan$100,000

I would like to create the tables like:

Selecting December

ProductCurrent MonthPrevious MonthVariance
Tran$350,000$400,000-$50,000
Time$150,000$100,000$50,000
Loan$100,000$150,000-$50,000

Selecting November

ProductCurrent MonthPrevious MonthVariance
Tran$400,000$500,000-$100,000
Time$100,000$400,000-$300,000
Loan$150,000$100,000$50,000

Selecting October

ProductCurrent MonthPrevious MonthVariance
Tran$500,000$0$500,000
Time$400,000$0$400,000
Loan$100,000$0$100,000

And eventually when selecting by Quater, Semester and Year to show the variance current balance, previous period balance and the variance. I have tried many set analysis ideas and creating variables but cant seem to get it to work properly.

1 Solution

Accepted Solutions
prabhuappu
Creator II
Creator II

Hi,

Please find the attached application.

For Month,

Previous : Sum({<Year={'$(=Max(Year)-1)'}>}Balance)

Current : Sum({<Year={'$(=Max(Year))'}>}Balance)

For Year,

Previous : Sum({<Month={'$(=monthname(Max(Month)-1))'}>}Balance)

Current : Sum({<Month={'$(=monthname(Max(Month)))'}>}Balance)

For Quarter,

Previous : Sum({<Quarter={'$(vQuarter1)'}, Year={$(vYear1)}>}Balance)

Current : Sum({<Quarter={'$(vQuarter)'}, Year={'$(vYear)'}>}Balance)

vYear=Max(Year)
vYear1=if((max(Quarter)-2)<=0,max(Year)-1,max(Year))
vQuarter=max(Quarter)
vQuarter1=mod(max(Quarter)-1,4) //'S'&if(mod(Quarter-2,4)=0,4,mod(Quarter-2,4))&'-'&if((Quarter-2)<=0,Year-1,Year)

Hope it helps...

Regards,

Prabhu Appu

View solution in original post

9 Replies
prabhuappu
Creator II
Creator II

Hi,

Please find the attached application.

For Month,

Previous : Sum({<Year={'$(=Max(Year)-1)'}>}Balance)

Current : Sum({<Year={'$(=Max(Year))'}>}Balance)

For Year,

Previous : Sum({<Month={'$(=monthname(Max(Month)-1))'}>}Balance)

Current : Sum({<Month={'$(=monthname(Max(Month)))'}>}Balance)

For Quarter,

Previous : Sum({<Quarter={'$(vQuarter1)'}, Year={$(vYear1)}>}Balance)

Current : Sum({<Quarter={'$(vQuarter)'}, Year={'$(vYear)'}>}Balance)

vYear=Max(Year)
vYear1=if((max(Quarter)-2)<=0,max(Year)-1,max(Year))
vQuarter=max(Quarter)
vQuarter1=mod(max(Quarter)-1,4) //'S'&if(mod(Quarter-2,4)=0,4,mod(Quarter-2,4))&'-'&if((Quarter-2)<=0,Year-1,Year)

Hope it helps...

Regards,

Prabhu Appu

Not applicable
Author

Hi Prabhu,

Should I be creating the variables within my script? or should I do it within the document properties and add a new variable? When I placed it in my script, it gave me a script line error...

Sincerely,

Maria

prabhuappu
Creator II
Creator II

Hi,

You need to create it either in document properties or variable Overview.

Capture2.PNG

Regards,

Prabhu Appu

Not applicable
Author

Thank you Prabhu.

I was able to get it to work, but now the problem with my data is that the month field comes with the month and year. Due to this, it messes up the look and field of the month select field. Is there anyway that I can do this, but eliminating the year from the month field?

Thanks for all your help!!

Sincerely,

Maria

Not applicable
Author

Hi,

You can split the filed by using the subfield function ex:

subfield(YearMonth,'-' , 1) as Year

subfield(YearMonth,'-' , 2) as Month

etc

prabhuappu
Creator II
Creator II

Hi,

As a best practice you can keep both and Month(Jan), Year and MonthName (Jan-2014) in you common Calendar. So that you can use those where ever needed.

My suggestion is to Create another field for month and use it in List box.

Regards,

Prabhu Appu

Not applicable
Author

Hi Pabhu,

I tried creating another field for the month within my master calendar already but when I select the month, the numbers dont adjust. Only when I select the month-year within the monthname field is when the numbers adjust in the current and previous tables you helped me create. This is what I created:

MonthName(TempDate) as Month,
Month(TempDate) as Month_SelectField,

Do I have to change the formulas in the tables to include the month field and not the monthname field?

Thanks!

Maria

Not applicable
Author

NVM Pabhu. I was able to figure it out. Thank you again for all the help! I appreciate the support

Not applicable
Author

Thank you Hampus. I was able to figure it out by creating an additional field for just the month name. I appreciate the help.