Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
i am showing 24 months data at a time in chart. I have data from 2003 to feb 2018 .
when i click on 2017 then its shows data for 16 & 17(24 months) &
when i click on 2018 it shows data for 2017 & two months 2018 (14 months).
but i want to show like when i click on 2018 then it will be show data from Mar16 to feb 18 (24 months).
i don't know how to show it or it is possible or not?
Thanks.
It can definitely be done.
if you are using set analysis for the expression(s) --> you will have to update that to month based, instead of year based.
you can identify the range as addmonths(max(Date), -23) to max(Date) - which should equate to March 2016 and Feb 2018 in your case.
If you need more clarification, please post a sample QVW.
Please paste a sample Application
PFA for the same.
Hi prachi,
I have seen the qvw, but I think there is one more thing needs to be confirmed.
What you want to show is 24 month's bar data or 24 month's data from the selections.
For example, in your sample, in 2018 only have Oct.
If you select 2018 year, what you want to show is 2016 Nov - 2018 Oct(it will have some null because there is no data in some months)
or
2015 Sep - 2018 Oct(total 24 months having data)?
Hope I have clarified what I want to ask.
Aiolos
consider i have only oct data in year 2018 and 2016&2017 have all months data then when i select year 2018 then its shows oct 18 to feb 2016(24 months)
Please see my attachment, I think you need a sort for the year and month, and use dimension limit.
Thanks
please share screen shot. i am not able to open file
t1:
LOAD Year & Month as Sort,
Year,
Month,
Value;
LOAD * INLINE [
Year, Month, Value
2005, Jan, 1
2005, April, 2
2005, Aug, 3
2006, Jan, 4
2006, Feb, 5
2006, Mar, 6
2006, April, 7
2006, May, 8
2006, Jun, 9
2006, July, 9
2006, Aug, 0
2006, Sep, 1
2006, Oct, 234
2006, Nov, 12
2006, Dec, 42
2007, Jan, 34
2007, Feb, 12
2007, Mar, 35
2007, April, 45
2007, May, 34
2007, Jun, 62
2007, July, 6
2007, Aug, 345
2007, Sep, 7
2007, Oct, 56
2007, Nov, 87
2007, Dec, 8
2008, Jan, 67
2008, Feb, 95
2008, Mar, 67
2008, April, 84
2008, May, 5
2008, Jun, 62
2008, July, 2
2008, Aug, 45
2008, Sep, 13
2008, Oct, 5
2008, Nov, 246
2008, Dec, 245
2009, Jan, 7
2009, Feb, 6458
2009, Mar, 47
2009, April, 8
2009, May, 679
2009, Jun, 6
2009, July, 74
2009, Aug, 67
2009, Sep, 3
2009, Oct, 6
2009, Nov, 24
2009, Dec, 53
2010, Jan, 5
2010, Feb, 425
2010, Mar, 245
2010, April, 2
2010, May, 546
2010, Jun, 24
2010, July, 56
2010, Aug, 35
2010, Sep, 84
2010, Oct, 8
2010, Nov, 47
2010, Dec, 84
2011, Jan, 56
2011, Feb, 234
2011, Mar, 5
2011, April, 2456
2011, May, 5
2011, Jun, 23
2011, July, 45
2011, Aug, 256
2011, Sep, 2
2011, Oct, 54
2011, Nov, 53
2011, Dec, 756
2012, Jan, 7
2012, Feb, 36
2012, Mar, 7
2012, April, 35
2012, May, 4673
2012, Jun, 465
2012, July, 3
2012, Aug, 475
2012, Sep, 37
2012, Oct, 36
2012, Nov, 57
2012, Dec, 5
2013, Jan, 76
2013, Feb, 4
2013, Mar, 76
2013, April, 456
2013, May, 846
2013, Jun, 78
2013, July, 47
2013, Aug, 7854
2013, Sep, 673
2013, Oct, 56
2013, Nov, 3
2013, Dec, 652
2014, Jan, 13
2014, Feb, 5
2014, Mar, 54
2014, April, 24
2014, May, 524
2014, Jun, 234
2014, July, 52
2014, Aug, 45
2014, Sep, 4
2014, Oct, 68
2014, Nov, 46
2014, Dec, 8
2015, Jan, 76
2015, Feb, 84
2015, Mar, 6
2015, April, 3
2015, May, 6
2015, Jun, 4
2015, July, 56
2015, Aug, 2
2015, Sep, 2
2015, Oct, 45
2015, Nov, 2
2015, Dec, 5
2016, Jan, 4
2016, Feb, 67
2016, Mar, 46
2016, April, 7
2016, May, 456
2016, Jun, 76
2016, July, 67
2016, Aug, 356
2016, Sep, 3
2016, Oct, 456
2016, Nov, 354
2016, Dec, 63
2017, Feb, 46
2017, May, 5
2017, Jun, 36
2017, July, 3
2017, Oct, 6
2017, Nov, 53
2017, Dec, 754
2018, Oct, 648
];
Dimension : Sort
Dimension Limits : First 24 Not show others
Expression : =sum({<Year={"<=$(=Var_max_date)"}>}Value)
Sort : Load Order Reversed
Prachi,
please add a column to your data model - which will make the calculation much easier.
sales:
LOAD *, date(Date#(Month & '/01/' & Year, 'MMM/DD/YYYY')) as Month_Year;
LOAD ID,
Name,
Region,
Sales,
Month
FROM
{E:\Qlik\QlikView\Sales.xlsx]
(ooxml, embedded labels, table is Sheet1);
Also, add 2 variables
vMaxDate
defined as "=max(Month_Year)"
vMaxDate24
defined as "=max({<Year = {"<=$(=Year)"}>} Month_Year, 24)"
so - this will give the required limts of the Set expression.
your chart can you use the dimension as =date(Month_Year, 'YY-MMM')
and expression as =SUM({<Year=, Month_Year={">=$(=vMaxDate24)<=$(=vMaxDate)"}>}Sales)
As the dimension is a date - you can simply sort it by numeric value "ascending".
Hope this resolves your concerns.
