topic Re: 24 months data in line chart in QlikView

24 months data in line chart

Anonymous — Fri, 06 Apr 2018 08:35:47 GMT

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.

Re: 24 months data in line chart

boorgura — Fri, 06 Apr 2018 14:40:41 GMT

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.

Thanks.

Re: 24 months data in line chart

passionate — Fri, 06 Apr 2018 14:54:53 GMT

Please paste a sample Application

Re: 24 months data in line chart

Anonymous — Mon, 09 Apr 2018 05:25:18 GMT

PFA for the same.

Re: 24 months data in line chart

uacg0009 — Mon, 09 Apr 2018 07:33:10 GMT

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)

2015 Sep - 2018 Oct(total 24 months having data)?

Hope I have clarified what I want to ask.

Thanks.

Aiolos

Re: 24 months data in line chart

Anonymous — Mon, 09 Apr 2018 07:59:58 GMT

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)

Re: 24 months data in line chart

uacg0009 — Mon, 09 Apr 2018 08:32:14 GMT

Hi prachi,

Please see my attachment, I think you need a sort for the year and month, and use dimension limit.

Thanks

Aiolos

Re: 24 months data in line chart

Anonymous — Mon, 09 Apr 2018 08:36:13 GMT

please share screen shot. i am not able to open file

Re: 24 months data in line chart

uacg0009 — Mon, 09 Apr 2018 09:01:24 GMT

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

Aiolos

Re: 24 months data in line chart

boorgura — Mon, 09 Apr 2018 14:25:08 GMT

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,

Year,

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.