How to calculate avg trendline.

[#1]

Hi all,

I have combo chart where I calculate the top 7 sale days in the last 41 days for four different branches.

My expression is:

Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales)

Dimension is:

=aggr(if(rank(fabs(Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales)))<=7,SaleDates),SaleDates)

My question is, what expression can I use to show a line of the average top 7 sale days? It works perfectly when I use the "average" trendlines, but I'd like to know how I can calculate this myself.

I've attached a picture of my chart, just to give some clarity.

Hope my question is clear, I'm still very new to Qlikview.

Thanks in advance.

[krishna_2644]

Post some sample data.

Thanks

[sunny_talwar]

May be this:

=Avg(Aggr(Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales), SaleDates))

[#4]

Hi Sunny,

This gives me the exact same values as my first expression, instead of the average value.

Thanks for the reply.

[sunny_talwar]

What about this:

=Avg(TOTAL Aggr(Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales), SaleDates))

[krishna_2644]

Try Unsing below expression as you want to calculate avg trend line for top 7 sales:

= avg(aggr(

    if(

       rank(

   Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))      <=$(=Date(Today()-1))"}>} Sales) ) <= 7,

  Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales)

    ),SalesDate)

)