topic Re: Re: Previous day sales in QlikView

Previous day sales

tresB — Tue, 27 Aug 2013 07:47:42 GMT

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.

Re: Previous day sales

MayilVahanan — Tue, 27 Aug 2013 08:09:07 GMT

PFA

Hope that helps

Re: Re: Previous day sales

tresB — Tue, 27 Aug 2013 08:16:22 GMT

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

Re: Previous day sales

MayilVahanan — Tue, 27 Aug 2013 08:28:21 GMT

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?

Re: Re: Previous day sales

tresB — Tue, 27 Aug 2013 08:41:38 GMT

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

Re: Previous day sales

kalyandg — Tue, 27 Aug 2013 11:03:56 GMT

hi tresesco,

try this

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

Thanks,

Kalyan

Re: Re: Previous day sales

tresB — Tue, 27 Aug 2013 11:15:32 GMT

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.

Re: Previous day sales

tresB — Tue, 27 Aug 2013 14:00:24 GMT

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..

Re: Previous day sales

tresB — Tue, 27 Aug 2013 19:45:11 GMT

Edit:

Re: Previous day sales

Gysbert_Wassenaar — Wed, 04 Sep 2013 07:56:02 GMT

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

Re: Previous day sales

tresB — Wed, 04 Sep 2013 08:20:14 GMT

Thanks a lot Gysbert ! It is a nice excercise and learning indeed for me. I will take a close look at it and come back. By the time, would request you to throw some light on - why " FirstSortedValue(Sum(Sales), -Date, RowNo(TOTAL)+1) " would not work. Had this Rowno() concept been worked, the solution could have been easier.

Thanks.