Closing balance on every day within period selection

[ulyxess1983]

Hi folks,

Have kind of problem - need to show closing balance in my warehouse turnover report on every day with selected period like this:

day     turnover     closing balance

day 1        0             100 pcs

day 2        -30             70 pcs

etc

full acumulation is a great option, BUT it doesn't work when i want to select period say day5-day10 (it doesn't calculate balance for day5!).

also tried to use variable vDateClosingBalance with Sum({<DimDate = {"<=($(=vDateClosingBalance))}">} Qty)

or Sum(<$<DimDate = {[$(=($(vDateClosingBalance))))]}>} Qty)

the same thing - it ideally works when NO period selected.

does anyone have an idea?

file attached.

thanks in advance!

Mike

[martin59]

Hi,

Is your variable in date or year format ?

If it's a date you have to do this :

Sum({<DimDate = {"<=$(=date(vDateClosingBalance))"}>} Qty)

Consider your DimDate field is in date format too.

Hope that helps you

Martin

[ulyxess1983]

Hi Martin,

Thanks for reply! But my variable is in date format (dd.mm.yyyy) and DimDate is also in date format (dd.mm.yyyy). I've already tried it - still doens't show balance on every day.

Thanks again for answer.

Mike

[martin59]

Have you tried this :

Sum({<DimDate = {"<=$(=num(vDateClosingBalance))"}>} Qty)

[ulyxess1983]

Tried.

Having DimDate = 03.08.2009...19.08.2009 and vDateClosingBalance = 19.08.2009

03.08.2009  -609,17

04.08.2009  -493,56

06.08.2009  0,00

07.08.2009  -122,78

10.08.2009  19 208,53

11.08.2009  -456,77

12.08.2009  -1 257,29

13.08.2009  -184,06

14.08.2009  -1 177,15

15.08.2009  -306,23

18.08.2009  -470,64

19.08.2009  0,00

and must be

03.08.2009  10 095,04

04.08.2009  9 601,48

06.08.2009  9 601,48

07.08.2009  9 478,70

10.08.2009  28 687,23

11.08.2009  28 230,46

12.08.2009  26 973,17

13.08.2009  26 789,11

14.08.2009  25 611,96

15.08.2009  25 305,73

18.08.2009  24 835,09

19.08.2009  24 835,09

(iits just sum(qty) with full accumulation option)

[#6]

as i understanded you want to have a start date and an end date and to sum the balance during this period

if so, you can use this

Sum ({$< Date ={'>=$(=vStartDate)<=$(=vEndDate)'}>}Balance)

put your start and end date in variables and use them in the set analysis

[ulyxess1983]

Hi mona,

Thanks for idea.

You understand it correctly. I did like you say, but when i add Date as dimension in a table it still shows Sums befor vStartDate and after vEndDate. Like this

vStart = 23/07/2009

vEnd = 30/07/2009

20.07.2009  19 214,47

23.07.2009  19 017,13

24.07.2009  14 622,53

27.07.2009  13 729,19

30.07.2009  13 514,40

31.07.2009  11 024,71

01.08.2009  10 704,21

03.08.2009  10 095,04

Any ideas?

Thanks

[#8]

Hi,

You can use a calculated dimension in date

like:

if(Date>=vStartDate and Date<=vEndDate,Date)

so this will replace the older date with null value

to hide it

in this dimension in the settings for it check (suppress when value is null)

try it

[ulyxess1983]

Yep, I did a calculated dimension,

but now it's calculating only qty on this date, no TO date

must be:

20.07.2009  19 214,47

23.07.2009  19 017,13

24.07.2009  14 622,53

i have:

23.07.2009  -197,34

24.07.2009  -4 591,94

27.07.2009  -5 485,28

Exp = Sum ({$<DATE ={'>=$(=vDateOpeningBalance) <=$(=vDateClosingBalance)'}>} WQTY)

Dim = =IF(DATE >= date(vDateOpeningBalance) AND DATE <= date(vDateClosingBalance), DATE)