topic Re: Last 3 months data in expression for a bar chart in Qlik Learning Discussions

Last 3 months data in expression for a bar chart

Mon, 09 Feb 2015 13:25:49 GMT

Hi all ,

i have 12 months of data , in which i would like to use last 3 months of data while displaying in bar chart.

for suppose i have feb 2014 to jan 2015 data , my last 3 months data should be jan 2015, dec 2014, Nov 2014

my dimensions are
terc_defect

monthterc

i need an expression to take last 3 months data

Thanks in advance

Re: Last 3 months data in expression for a bar chart

Anonymous — Mon, 09 Feb 2015 19:56:57 GMT

Hi,

you can use Addmonths( max(date), 0), Addmonths( max(date), -1), Addmonths( max(date), -2) in your set analysis

Elena

Re: Last 3 months data in expression for a bar chart

maxgro — Mon, 09 Feb 2015 20:03:22 GMT

here a possible answer

Relative Calendar Fields

Re: Last 3 months data in expression for a bar chart

robert_mika — Mon, 09 Feb 2015 22:01:42 GMT

Look here:

Last 3 Months Data in a Line Chart

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 05:21:44 GMT

Thanq all ,

Hi Elena

could you help me writing the set analysis , i have two dimensions Monthterc and terc_defectcategory.

Thanks in advance.

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 05:23:26 GMT

Could you post small sample of your data?

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 06:04:11 GMT

Try this:

Directory;

LOAD terc_defect,

monthterc,

Value

FROM

[151270.xlsx]

(ooxml, embedded labels, table is tab1);

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 06:05:22 GMT

terc	Defect Category
10/17/2014 0:00	Application Configuration
10/16/2014 0:00	Requirements
10/28/2014 0:00	Application Configuration
10/28/2014 0:00	Application Configuration
10/27/2014 0:00	Application Configuration
10/31/2014 0:00	Code
10/28/2014 0:00	Code
10/16/2014 0:00	Application Configuration
10/16/2014 0:00	Environment - Third Party
10/16/2014 0:00	Application Configuration
10/16/2014 0:00	Application Configuration
10/20/2014 0:00	Application Configuration
10/20/2014 0:00	Application Configuration
10/7/2014 0:00	Environment
10/10/2014 0:00	Code
10/8/2014 0:00	Tester Error
10/9/2014 0:00	Low Level Design
10/2/2014 0:00	High Level Design
10/17/2014 0:00	High Level Design
10/1/2014 0:00	Low Level Design
10/8/2014 0:00	Code
10/9/2014 0:00	Code
10/3/2014 0:00	Code
10/9/2014 0:00	Code
10/9/2014 0:00	Code
10/17/2014 0:00	Code
10/29/2014 0:00	Application Configuration

i gave month(terc) as monthterc

so my dimensions are monthterc and [ terc_defect category]

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 06:06:50 GMT

this is for october month data , the same way i have nov and december and also january data in another excel sheets .

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 06:16:42 GMT

What do you want to achieve on the bar?

Count the category?

Unless you have got large amount of your data it would be good practise to put them all in one sheet.

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 06:24:38 GMT

yes i want to count the category ,yes i do have many files and it is a large data set

when i am using your formula . i am getting all months in the data set

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 06:27:42 GMT

This formula was for data set I have posted not for yours

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 06:36:28 GMT

oh ok , but can you tell me what monthstart(max(monterc),-3 function gives ?

and also what would be desired expression for my data , do you need any more input robert?

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 06:37:28 GMT

Try

Script:

Directory;

LOAD Date(terc) as monthterc,

[Defect Category]

FROM....

Expression:

count({<monthterc={'>$(=monthstart(max(monthterc),-2))'}>}[Defect Category])

Re: Last 3 months data in expression for a bar chart

Tue, 10 Feb 2015 13:10:44 GMT

hey thanx robert it is working , but now what if i have to calculate average on x axis

my expression is average = sum(defect category)/count(defect category)

how do i use set analysis in this context .

Re: Last 3 months data in expression for a bar chart

robert_mika — Tue, 10 Feb 2015 14:01:49 GMT

Can you say what are you trying to achieve?

You have only Text value so first there is no Sum of any kind and then if you need average with Set Analysis we need a condition.

Re: Last 3 months data in expression for a bar chart

Wed, 11 Feb 2015 04:40:23 GMT

Hi Robert,

days_open	Enterprise system
19	Singleview
6	Portal
49	Android App
69	Migration Activity
158	Migration Activity
69	Migration Activity
61	Appsuite
132	Appsuite
46	Migration Activity
56	Migration Activity
32	Appsuite
48	Migration Activity
151	Appsuite
73	Appsuite
69	Migration Activity
55	Migration Activity
69	Migration Activity
77	Migration Activity
55	Migration Activity
59	Appsuite
55	Migration Activity
48	Migration Activity
131	Migration Activity
185	Mail Client
55	Migration Activity
69	Migration Activity
59	Mail Client
74	Migration Activity

now my expression is average = sum(daysopen)/count(enterprise system)

dimensions are enteprise system and monthterc

now i have to limit months using set analysis ?

let me know if you need any more output?

Thanks in advance

Re: Last 3 months data in expression for a bar chart

robert_mika — Wed, 11 Feb 2015 04:45:44 GMT

I would rather have the full set of data(all 3 columns) or a Excel spreadsheet...

If you can please attached.

Re: Last 3 months data in expression for a bar chart

Wed, 11 Feb 2015 05:09:49 GMT

days_open	Enterprise system	Montterc
19	Singleview	11/11/2014 0:00
6	Portal	11/11/2014 0:00
49	Android App	11/14/2014 0:00
69	Migration Activity	11/14/2014 0:00
158	Migration Activity	11/27/2014 0:00
69	Migration Activity	11/27/2014 0:00
61	Appsuite	11/26/2014 0:00
132	Appsuite	11/27/2014 0:00
46	Migration Activity	11/11/2014 0:00
56	Migration Activity	11/20/2014 0:00
32	Appsuite	11/6/2014 0:00
48	Migration Activity	11/12/2014 0:00
151	Appsuite	11/11/2014 0:00
73	Appsuite	11/27/2014 0:00
69	Migration Activity	11/27/2014 0:00
55	Migration Activity	11/14/2014 0:00
69	Migration Activity	11/26/2014 0:00
77	Migration Activity	11/3/2014 0:00
55	Migration Activity	11/13/2014 0:00
59	Appsuite	11/3/2014 0:00
55	Migration Activity	11/3/2014 0:00
48	Migration Activity	11/3/2014 0:00
131	Migration Activity	11/4/2014 0:00
185	Mail Client	11/3/2014 0:00
55	Migration Activity	11/24/2014 0:00
69	Migration Activity	11/19/2014 0:00
59	Mail Client	11/19/2014 0:00
74	Migration Activity	11/3/2014 0:00

hope it helps

Re: Last 3 months data in expression for a bar chart

robert_mika — Wed, 11 Feb 2015 05:45:40 GMT

=avg({<Montterc={'>$(=monthStart(max(Montterc),-2))'} ,[Enterprise system]= >}days_open)

Script:

LOAD days_open,

[Enterprise system],

date(Montterc) as Montterc

FROM

[151270.xlsx]

(ooxml, embedded labels, table is tab1);