topic Re: Set Analysis for last 13 months of Data in QlikView

Set Analysis for last 13 months of Data

Anonymous — Tue, 02 Sep 2014 15:00:15 GMT

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

Re: Set Analysis for last 13 months of Data

Tue, 02 Sep 2014 15:08:08 GMT

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

Re: Set Analysis for last 13 months of Data

MK_QSL — Tue, 02 Sep 2014 15:17:58 GMT

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)

Re: Set Analysis for last 13 months of Data

jagan — Tue, 02 Sep 2014 15:21:46 GMT

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.

Re: Set Analysis for last 13 months of Data

Anonymous — Tue, 02 Sep 2014 15:25:54 GMT

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.

Re: Set Analysis for last 13 months of Data

Anonymous — Tue, 02 Sep 2014 15:28:01 GMT

Thanks for the Answer's..

Sorry i didn't mention this earlier.

Re: Set Analysis for last 13 months of Data

sorrakis01 — Tue, 02 Sep 2014 15:30:47 GMT

Hi Ankit,

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

C y,

Re: Set Analysis for last 13 months of Data

MK_QSL — Tue, 02 Sep 2014 15:31:49 GMT

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

Re: Set Analysis for last 13 months of Data

jagan — Tue, 02 Sep 2014 15:32:38 GMT

Hi,

Try creating the Fiscal Calendar using this link

Fiscal and Standard Calendar generation

Regards,

Jagan.

Re: Set Analysis for last 13 months of Data

Anonymous — Wed, 03 Sep 2014 14:37:41 GMT

Thanks Manish, This was working..

Re: Set Analysis for last 13 months of Data

Anonymous — Wed, 03 Sep 2014 14:38:27 GMT

THANK YOU Every one, Different way of calculation was useful in lot of ways

Re: Set Analysis for last 13 months of Data

jagan — Wed, 03 Sep 2014 15:17:11 GMT

Hi Ankit,

I think this is the best expression, even if you select year or month this will always show last 13 months amount

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

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis for last 13 months of Data

MK_QSL — Wed, 03 Sep 2014 15:18:44 GMT

Hi Ankit,

What Jagan is saying is right... The formula I have provided need to be edited by using ByPASS of certain fields..

Year=, Month = ,

Re: Set Analysis for last 13 months of Data

Anonymous — Wed, 03 Sep 2014 17:35:50 GMT

Thanks Jagan..