Bar chart with 5motnhs average help

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

I have a requirement like below

In the below bar chart I have to apply current month to last 5months restriction and need to add one more value called "5 Month avg" to "MonthYear" field and in that value need to show the last  5 motnhs average like expected bar image.

Please find the below expected bar and actual bar images and please find the below attached source and qliksense app.

Actual bar:

Expected bar:

[Channa]

i will try to give an idea

u will have to have 2 select statements

1.you pull last 6 months by month

2.you pull avg of last 6 months

select month,count(Visitors) from table Group by Month

concatenate

select 'AvgMonth',avg(NoofVisits) from

(select month,count(Visitors) as NoofVisits   from table Group by Month) a

try 

[VishalWaghole]

https://community.qlik.com/t5/New-to-Qlik-Sense/Aggregated-Dimension-value/m-p/1550560#M128457

Hope this thread is useful for you.

Thanks and Regards,
Vishal Waghole

[mahitham]

Hi @Vishal
Could you please provide the solution with attached app and source.
Thanks in advance

[Channa]

bro try this

WEB:
LOAD
"Date",
MonthName(Date) as MonthYear,
"New Visitors" as NewVisitors,
"Unique Visitors" as UniqueVisitors,
"Bounce Rate" as BounceRate
FROM [lib://TEST/Web Analytics.xlsx]
(ooxml, embedded labels, table is Sheet1);

WEBSUM:
Load
MonthYear,SUM(NewVisitors) as NumofVisits resident WEB group by MonthYear ;
drop Table WEB;

Concatenate(WEBSUM)
load 'ALL' as MonthYear , avg(NumofVisits) as NumofVisits Resident WEBSUM group by 'ALL'

please find the attach for qvf

[mahitham]

Hi @Channa 

Thanks for your solution.

As per the requirement need to show only 5 Months average i.e.,Nov 2017 to Jul 2017(4015+3777+4186+3940+3851) /5 =3983 in the Average Bar not all the Months average.

Could you please help me on this.

[Channa · Accepted solution]

ur getting data from Database or XLS

try to restrict while loading

or update script--it is last 6 moths from today not 2017

let hDate = date(Monthstart(today(),-1));
let dDate = date(Monthend(today(),-6));

WEB:
LOAD
"Date",
MonthName(Date) as MonthYear,
"New Visitors" as NewVisitors,
"Unique Visitors" as UniqueVisitors,
"Bounce Rate" as BounceRate
FROM [lib://TEST/Web Analytics.xlsx]
(ooxml, embedded labels, table is Sheet1)
where date(Date)>=date('$(dDate)') and date(Date)<=date('$(hDate)');

WEBSUM:
Load
MonthYear,SUM(NewVisitors) as NumofVisits resident WEB group by MonthYear ;
drop Table WEB;

Concatenate(WEBSUM)
load 'ALL' as MonthYear , avg(NumofVisits) as NumofVisits Resident WEBSUM group by 'ALL'