Problem getting previous values using Peek without getting duplicates.

[aendr3w]

Hello.

I have a situation where I need to get the average sales per day from the 10 last non zero values (week days) presented daily. This is later used in a line chart to get a smoother curve.

in my current solution i'm using Peek in script and rangeavg in set analysis. But this way i'm not getting the previous 10 values, i'm simply getting the previous 10 calendar days with the gaps (weekends with no sales records) filled with previous values. I don't want to use the same value twice, and other factors make it impossible to discard/hide Null values as i'm comparing current year to previous year in a chart.

this is a sample of my code (siplified)

script:

TEMP:
Load [Date],
money(sum([Net Amount Order])) as sumSales
resident Data group by [Date];

PEEK:
Load
[Date],
if(sumSales=0,peek(sumSales,-1),sumSales) as sumSales
resident TEMP order by [Date];
drop table KLJTEMP;

Set analysis:

rangeavg(above(sum(sumSales),0,10))

This is my result in a straight table without the rangeavg:

There must be a way to solve this but i can't seem to figure it out. I would be most grateful for suggestions to nudge me the right direction.

/ Andreas

[aendr3w]

Anyone??

[marcus_sommer]

I think I wouldn't try it in this way else just using a different date-field which just contains the working-days or using such working-day information within a flag-field and then something like:

sum({< WorkingDayFlag = {1}>} Value)

as basic-expressions for the cumulation.

- Marcus 

[aendr3w]

Since I'm comparing current year to previous year in a line chart using DayNumberOfYear([Date]) as a dimension, I would still get zero values using that method.

[marcus_sommer]

I think you should synchronize the daynumberofyear() between all years with an appropriate offset - usually just with an offset of +1 - because otherwise you don't compare the same (week)days against each other.

- Marcus

[aendr3w]

That would cover most of the year but still wouldn't work around holidays and on bridge days as they accur on different days every year.

[marcus_sommer]

If there are moving holidays or further exceptions different between the years you will mandatory get gaps in one of your lines if you used a daynumberofyears (with/without any offset-logic) but is this really wrong from an analytical point of view?

If both lines should be always display the last 10 values of working-days regardless if the real dates behind it may for example belong to March or April depending on which day is eastern you just need to cumulate your working-days within the master-calendar and use it as dimension.

- Marcus