Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Post some sample data.
Thanks
May be this:
=Avg(Aggr(Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales), SaleDates))
Hi Sunny,
This gives me the exact same values as my first expression, instead of the average value.
Thanks for the reply.
What about this:
=Avg(TOTAL Aggr(Sum({$<Branch *= {'BRANCH1', 'BRANCH2', 'BRANCH3', 'BRANCH4'}, SaleDates = {">=$(=Date(Today()-41))<=$(=Date(Today()-1))"}>} Sales), SaleDates))
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)
)