Compare dates in Set Analysis

[catunoho]

Hi all,

have a problem with a chart where I want to show turnover by date (Year) relating to the Year when first turnover has been created. My chart should show for 2014 total turnover for all parts with first turnover in 2014, same for 2013...

I have created a calendar for the turnover date and selected the min turnover date to find out when the first turnover happened. Both dated are formatted to YEAR.

In the chart I use the following Set Analysis function but do not get any result:

sum({$<FirstTO_Year={"$(=Year)"}>}(Value))

Any ideas ???

Thanks

[mdmukramali]

Dear Stepan,

Create a Variable which stores a value of the MinYear

vMinYear=Min(Year field)                                   //  FirstTO_Year

sum({$<FirstTO_Year={"$(=vMinYear)"}>}(Value))

Thanks,

Mukram.

[catunoho]

Hello Mukram,

thanks for your response. Don't I get only one Year as First turnover Year if I put that into a variable ? The first turnover Year could be every Year between 2001 and 2014 and I want to see only turnover in the chart for each year that had the first turnover in the same year.

[mdmukramali]

Dear,

can you attach the sample file so that we can help you in a better way.

Thanks,

Mukram

[#5]

Hi Stefan,

You may be able to use the method suggested by Mukram is a slightly different way to get your result.

You could try creating a key and then using the group by function in order to get the FirstTO_Year.

Essentially just add:

Field1+Field2+Field3+Field4...... as KEY

then just create a new table

Min(Year Field) as FirstTO_Year,

KEY

Resident NormalTable

Group By KEY;

This will then give you the first TO year for each key, so basically you just need to create a key from all the fields you want to differentiate the turnover by. Sorry if this isn't what you are after, I can't open any QVWs for today.

Cheers,

Tyler

[flipside · Accepted solution]

I think this works ...

=sum(if(FirstTO_Year=Turnover_Year,Value,0))

flipside

[catunoho]

Hi flipside,

that works...it was just too easy to find the right way. Thanks...