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: 
Anonymous
Not applicable

Set Analysis for last 13 months of Data

Hi Friends,

Attached his the file which contains 3 years of data, i want to build a LINE chart via set analysis which shows last 13 months of Amount as of today. Please any one can help me on this.

Regards,

Ankit Month

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

You can do some changes in your script...

Load

  Year,

  Date#(Month,'MMM') as Month,

  Date(Date#(Month,'MMM'),'MM') as NumMonth,

  Year&Date(Date#(Month,'MMM'),'MM') as YearMonth,

  Amount

Inline

[

  Year, Month, Amount

  2012, Jan, 20000

  2012, Feb, 30000

  2012, Mar, 40000

  2012, Apr, 50000

  2012, May, 60000

  2012, Jun, 70000

  2012, Jul, 80000

  2012, Aug, 90000

  2012, Sep, 100000

  2012, Oct, 110000

  2012, Nov, 120000

  2012, Dec, 130000

  2013, Jan, 140000

  2013, Feb, 150000

  2013, Mar, 30000

  2013, Apr, 60000

  2013, May, 90000

  2013, Jun, 120000

  2013, Jul, 150000

  2013, Aug, 180000

  2013, Sep, 210000

  2013, Oct, 240000

  2013, Nov, 270000

  2013, Dec, 300000

  2014, Jan, 330000

  2014, Feb, 360000

  2014, Mar, 390000

  2014, Apr, 420000

  2014, May, 450000

  2014, Jun, 480000

  2014, Jul, 510000

  2014, Aug, 540000

];

Now create a line chart

Dimension

YearMonth

Expression

=SUM({<YearMonth = {"<=$(=Date(MonthStart(Today()),'YYYYMM')),'YYYYMM'))>=$(=Date(MonthStart(Today(),-12),'YYYYMM'))"}>}Amount)

View solution in original post

13 Replies
Not applicable
Author

Hi

You first need to make sure you create a true date when you load the data into QlikView. Having a true date field always makes working with the data in expressions much easier. You script would be something like this:

LOAD

     Year,

     Month,

     date#('01/' & Month & '/' & Year, 'DD/MMM/YYYY')  AS Date,

     Amount

From ........

With this loaded you can then use the below set analysis to get the last 13 months (assuming you are including this month)

sum({<Date = {">=$(addmonths(monthstart(today()), -12) <=$(today())"}>} Amount)

Regards

Matt

MK_QSL
MVP
MVP

You can do some changes in your script...

Load

  Year,

  Date#(Month,'MMM') as Month,

  Date(Date#(Month,'MMM'),'MM') as NumMonth,

  Year&Date(Date#(Month,'MMM'),'MM') as YearMonth,

  Amount

Inline

[

  Year, Month, Amount

  2012, Jan, 20000

  2012, Feb, 30000

  2012, Mar, 40000

  2012, Apr, 50000

  2012, May, 60000

  2012, Jun, 70000

  2012, Jul, 80000

  2012, Aug, 90000

  2012, Sep, 100000

  2012, Oct, 110000

  2012, Nov, 120000

  2012, Dec, 130000

  2013, Jan, 140000

  2013, Feb, 150000

  2013, Mar, 30000

  2013, Apr, 60000

  2013, May, 90000

  2013, Jun, 120000

  2013, Jul, 150000

  2013, Aug, 180000

  2013, Sep, 210000

  2013, Oct, 240000

  2013, Nov, 270000

  2013, Dec, 300000

  2014, Jan, 330000

  2014, Feb, 360000

  2014, Mar, 390000

  2014, Apr, 420000

  2014, May, 450000

  2014, Jun, 480000

  2014, Jul, 510000

  2014, Aug, 540000

];

Now create a line chart

Dimension

YearMonth

Expression

=SUM({<YearMonth = {"<=$(=Date(MonthStart(Today()),'YYYYMM')),'YYYYMM'))>=$(=Date(MonthStart(Today(),-12),'YYYYMM'))"}>}Amount)

jagan
Luminary Alumni
Luminary Alumni

Hi Ankit,

Try like this

Data:

LOAd

*,

MakeDate(Year, Match(Month,'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')) AS Date

FROM DataSource;

=Sum({<Year=, Month=, Date={'>=$(=MonthStart(Max(Date), -12))<=$(=Max(Date))'}>} Amount)

Regards,

Jagan.

Anonymous
Not applicable
Author

Thanks for the Answer's.. 

My problem is, i have given the sample data but i have complex calculation for the amount. As i have a fiscal Year and Month is it not possible i can write a set analysis in the DIMENSION for showing the last 13 Fiscal month&Year. So my DIMENSION would restrict the 13 months and in the Expression i can write to calculate the Amount Logic.

Sorry i didn't mention this earlier.

Anonymous
Not applicable
Author

Thanks for the Answer's..

My problem is, i have given the sample data but i have complex calculation for the amount. As i have a fiscal Year and Month is it not possible i can write a set analysis in the DIMENSION for showing the last 13 Fiscal month&Year. So my DIMENSION would restrict the 13 months and in the Expression i can write to calculate the Amount Logic. As i want to show the data on Fiscal Month and Year.

Sorry i didn't mention this earlier.

sorrakis01
Specialist
Specialist

Hi Ankit,

Try to create an IDPeriod 'yyyymm' (Year & Month) and it resolves your problems.

C y,

MK_QSL
MVP
MVP

Provide some sample data otherwise difficult to understand your actual data model..

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try creating the Fiscal Calendar using this link

Fiscal and Standard Calendar generation

Regards,

Jagan.

Anonymous
Not applicable
Author

Thanks Manish, This was working..