Solved: Re: Last 3 months data in expression for a bar cha... - Page 2 - Qlik Community

Report Inappropriate Content · ‎2015-02-09

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

Report Inappropriate Content · ‎2015-02-10

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

robert_mika · ‎2015-02-10

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

Report Inappropriate Content · ‎2015-02-10

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?

robert_mika · ‎2015-02-10

Try

Script:

Directory;

LOAD Date(terc) as monthterc,

[Defect Category]

FROM....

Expression:

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

Report Inappropriate Content · ‎2015-02-10

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 .

robert_mika · ‎2015-02-10

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.

Report Inappropriate Content · ‎2015-02-10

Hi Robert,

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

robert_mika · ‎2015-02-10

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

If you can please attached.

Report Inappropriate Content · ‎2015-02-11

hope it helps

robert_mika · ‎2015-02-11

=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);

Last 3 months data in expression for a bar chart