count how many rows above current row totals in 3000$

[wizardo]

hi,

let me try to explain....

I have a table with few dimensions and one expression

I need for each raw to add a new field which  will count how many rows ABOVE each raw makes up a certain amount

for example:

if I have a table with sales per day, I want to know how many days totals in 3000 (E.G.)

its kinda like doing a rolling sum based on count just the opposite, meaning doing a rolling count based on sum

the reason i need the number of rows is because then i can use this number of rows to calculate a rolling avg on another expression in the table

I don't mind if it is done in the script. i prefer some more dynamic way of doing this but any idea will work

the best iv come up with until now is reading one raw at a time in the script and loop on the previous rows till i get the sum the count the number of loops

i can also do it in a macro

but i thought maybe some of you can start a new idea , one that is more QVway

any ideas?

Mansyno

Message was edited by: wizardo: changed the title of the discussion

[nagaiank]

An example of doing what you want is attached.

Hope this helps.

[wizardo]

hmm

im sorry but i guess i didnt explain very well

i dont need the count of days in which sales on that day ware more then 3000

i need the count of days before each row that SUMS up to 3000

for example if im on the 30/01 and i have 300 sales per day then the count will be 10days above

ofcourse if the sales ware equal eac hday it would be easy

that means that 300 can be made of difrent number of rows for each record

hope that clerifies it

Mansyno

[nagaiank]

I do not understand your requirement.

If you give an example with sample data, the result that you want to see for the sample data and how you want to use this result in your subsequent steps if any (such as calculating the rolling average that you mentioned in your original post), someone in the forum may be able to help.

[wizardo]

hi again,

ill try to explain again

for each raw in the table

i want to look in the rows above it

and count how many rows till i get 3000 in sales

this shuld be done for each row

for example if i have

date,     sales$ rows_count

01   ,     1000,     - //this is null because i have not reached 3000

02   ,     1000,     -

03   ,     1000,     -

04   ,     1500,     3 //rows 01+02+03=3000

05   ,     1500,     2 //rows 04+03=2500 if i will add row 03 it will exceed 3000 so it only counts 2 rows

06   ,     2000,     2 //rows 05+04=3000

07   ,     1000,     1 //row 06 =2000 //if i will add row 5 it will exceed 3000 so it only counts 1 row

08   ,     1000,     2 //rows 07+06=3000

hope it is more clear now

Mansyno

[wizardo]

it seems no one has a solution:(

i know i can do it using some looping methods (e.g. reading one record each time and then lop on the previous records till i gather 3000$)

but that will rather make my table fixed and not dynamic.

i will do it if no other solution but i prefer something more dynamic in nature

maybe calculatting somthing in the script and the rest in the layout or anything like that

all help is appreciated

Mansyno