Previous day sales

[tresB]

Hello Everybody,

It looks pretty easy, but even after exploiting the head for quite a long time i could not figure out. Sure that experts here would be able to help.

Company	Date	Sales for the day	PreviousDaySales
A	27-08-2013	SUM(Sales)	Sum( Sales as on 25th)
A	25-08-2013	SUM(Sales)	Sum( Sales as on 19th)
A	19-08-2013	SUM(Sales)	Sum( Sales as on the day sales took place before 19th)

Have attached a qvw, please have a look.

Note: I don't want to use BELOW() function, neither I want to achieve this in the script(AsOfDate approach i am aware of). Please suggest an expression, might be using set analysis with functions like , AGGR, RANK, FIRSTSORTEDVALUE.

Thanks a lot.

[MayilVahanan]

Hi

PFA

Hope that helps

[tresB]

Thanks Mayil+Vahanan Ramasamy for the reply. However, as i mentioned earlier, i am looking for a front end solution .

[MayilVahanan]

HI tresesco,

I think its difficult process to achieve this. Can you able to say why you goes for front end solution, instead of backend or below function?

[tresB]

Hi Ramasamy,

Very often I have been facing this requirement. This becomes very tough to change the existing underlaying data model which is running for couple of months/years now. And this type of requirement is very common if you have noticed that. That is why, i am looking for a front end solution. May be somebody has explored and found a solution. I tried a logic using RowNo(Total) , but unfortunately rowno() does not work with FirstSortedValue(Please sombody prove me wrong).

i tried something like this:

FirstSortedValue(Sum(Sales), -Date, RowNo(TOTAL)+1)    // unfortunately rowno() here always returns 0

[kalyandg]

hi tresesco,

try this

rangesum(below(Total Sales, 0, rowno(Total)))

Thanks,

Kalyan

[tresB]

Hello Sundaram, thanks for your kind reply.

No, it does not work either. You can try with the attached qvw. I suspect the total keyword does not work as expected. Moreover, I would really appreciate if somebody can suggest a solution without using below() function and script work.

[tresB]

I would like to bring this thread on top for your attention. Please share your thoughts. Is it really not possible in the front end?

Thanks a lot..

[tresB]

Edit:

[Gysbert_Wassenaar]

Well, if you really want to do things the hard way... see attached example. Not recommended, but a nice exercise