Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

QlikSense Average

Hi all,

i want to find average based on week days.i.e if 1 monday has passed in a month then value/1,if 2 monday or tue wed etc has passed in a month then sum(value) for 2 monday or tue or wed etc /2 and so on

 

please find screenshot i want to find average columi want to find average colum

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

1) Create a Month Field in your script:

Month(Date) as Month.

 

2) Use a simple table chart and put:

As dimensions:

Month

Date,

week

As measures:

YourMeasure (for testing) : sum(Measure)

Cumulative by weekDay (by Month)

aggr(
RangeSum(Above(
sum(measure)
,0,RowNo())),Month, Week,Date)

Nb of occurences of a weekday in a month:

aggr(rangesum(above( count({1}Week),0,RowNo())),Month,Week,Date)

 

Average : cumul/nb : 

aggr(
RangeSum(Above(
sum(measure)
,0,RowNo()))
/
rangesum(above( count({1}Week),0,RowNo()))

,Month, Week,Date)

 

Result:

Capture.PNG

View solution in original post

8 Replies
Highlighted
Partner
Partner

1) Create a Month Field in your script:

Month(Date) as Month.

 

2) Use a simple table chart and put:

As dimensions:

Month

Date,

week

As measures:

YourMeasure (for testing) : sum(Measure)

Cumulative by weekDay (by Month)

aggr(
RangeSum(Above(
sum(measure)
,0,RowNo())),Month, Week,Date)

Nb of occurences of a weekday in a month:

aggr(rangesum(above( count({1}Week),0,RowNo())),Month,Week,Date)

 

Average : cumul/nb : 

aggr(
RangeSum(Above(
sum(measure)
,0,RowNo()))
/
rangesum(above( count({1}Week),0,RowNo()))

,Month, Week,Date)

 

Result:

Capture.PNG

View solution in original post

Highlighted
Creator II
Creator II

not working for me
can you please sort your date and send screenshot
Highlighted
Partner
Partner

You know? I'm on a mission right now, I have my own projects and this did not keep me from creating apps and inline models to try to help community members because I know how it feels to not know the answer; I've been there.

So please, AT LEAST, you can thank anyone willing to sacrifice time and effort to replicate ur data, search for a solution and contribute to your well being.

That said, this'll work if u replicate exactly what I shared with you, even if you sort by date:

Capture.PNG

Have a nice day.

Highlighted
Creator II
Creator II

thanks a lot.
i Aptreciate your effort to help.
Highlighted
Creator II
Creator II

same thing i tried but it is not working i don't know why

Capture1.PNG

Highlighted
Partner
Partner

can u share ur app?

a sample app maybe with only this table/data?

Highlighted
Creator II
Creator II

sure...
thanks
Highlighted
Creator II
Creator II

working fine now.

had problem with date format.

Thanks a lot