Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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)
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.
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.
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.
Hi Ankit,
Try to create an IDPeriod 'yyyymm' (Year & Month) and it resolves your problems.
C y,
Provide some sample data otherwise difficult to understand your actual data model..
Hi,
Try creating the Fiscal Calendar using this link
Fiscal and Standard Calendar generation
Regards,
Jagan.
Thanks Manish, This was working..