Set Analysis: YTD up to a max period.

[#1]

I have data arranged by year and period. My selection will be Year and Period. I can sum (value) for the selected period but I want to use set analysis to total the value up until the period selected for only the year selected. For example I select Year 2013 and period 3. Period 3 value is 500.,  I want to show value for period 3 and the accumulated values of periods 1 to 3 as YTD so far.

This exprssion give me the same as sum(Value).

Sum({$<Per={"<=$(=max(Per))"}>} Value)

if I change the expression to use all values the {1) I get previous years.

or when I try to include all data and limit year and period I get nothing.

Sum({1<Per={"<=$(vMaxPer)"}, {<Year={"<=$(vMaxYear)"} >} Tons)

[tresesco]

Your first expression: Sum({$<Per={"<=$(=max(Per))"}>} Value) should work. I guess the format of Per field is the issue. How is the data in your Per field?

[v_iyyappan]

Hi,

Try like this

=Sum({<Year={$(=vMaxYear)}, Per={"<=$(=vMaxPer)"}>}Tons)

regards,

[#4]

Hi Tresesco,

Thanks for your reply. I select 2013 and April (Period 4). Both Period 4 and YTD are the same. I want YTD to = all period up to selected period.

Thanks for your prompt reply.

Ed Wickstrom

Information Systems Manager

Mpact Corrugated

Tel: +27 21 507 6700 Fax: +27 86 520 3385 Cell: +27 82 807 0790

Cnr Bofor Circle and Losack Ave, Epping 2, Epping

Western Cape

7475

EWickstrom@mpact.co.za www.mpact.co.za

[tresesco]

You are selecting in Year and Month fields and probably not in Per. The field names and the data in the fields do matter while you are thinking of writing set expression. If your Month field is generated in the script using month() function, expression something like - Sum({$<Month={"<=$(=max(Month))"}>} Value) should work.

Best would be if you can share your sample app with sample data. 

[#6]

Hi Friend,

When I use your equation the period total and YTD up to that period are still the same.

If I modify your equation to include all data

=Sum({1<Year={$(=vMaxYear)}, Per={"<=$(=vMaxPer)"}>}Tons)

Then I get total for all periods not only those up until period 3 .

For customer ABA010 Jan= 1.7 Feb = 0 March = 1.6 tons , Jun = 1.5. . YTD should = 3.3 not 4.8 which is all periods.

Ed Wickstrom

Information Systems Manager

Mpact Corrugated

Tel: +27 21 507 6700 Fax: +27 86 520 3385 Cell: +27 82 807 0790

Cnr Bofor Circle and Losack Ave, Epping 2, Epping

Western Cape

7475

EWickstrom@mpact.co.za www.mpact.co.za

[#7]

Hi Try this for YTD,

=sum({<Date={">=$(Vmaxyear)<=$(Vmaxdate)"}>}Value)

Vmaxdate=max(Date)

Vmaxyearstart= Yearstart(Vmaxdate)

Accumulate YTD:

=rangesum(above(sum({<Date={">=$(Vmaxyear)<=$(Vmaxdate)"}>}Value)),0,rowno())

hope it will help

[#8]

Hi Tresesco,

See model attached. You can look at the YTD Tons as well where I have attempted different solutions.

Ed Wickstrom

Information Systems Manager

Mpact Corrugated

Tel: +27 21 507 6700 Fax: +27 86 520 3385 Cell: +27 82 807 0790

Cnr Bofor Circle and Losack Ave, Epping 2, Epping

Western Cape

7475

EWickstrom@mpact.co.za www.mpact.co.za

[tresesco]

Your sample has data for only one month, so can't be tested the logic. You may try : Sum({$<Mth=,Per={"<=$(=max(Per))"}>} Tons)  // which would allow you to select in the month field. Otherwise please upload sample app with atleast two months.

[#10]

Hi Edward,

this will give you values for the selected month

=SUM({<Vte.annee={'$(vMaxYear)'}, Vte.Date_AMJ01={'=$(vMaxDate)'} >} Vte.amount)

and this as YTD from january to selected month

=SUM({<Vte.annee={'$(vMaxYear)'}, Vte.Date_AMJ01={'<=$(vMaxDate)'} >} Vte.amount)

Note Date_AMJ01 equals 1st day of the month as e.g. 01/04/2013

best regards

chris