Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think this works ...
=sum(if(FirstTO_Year=Turnover_Year,Value,0))
flipside
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.
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.
Dear,
can you attach the sample file so that we can help you in a better way.
Thanks,
Mukram
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
I think this works ...
=sum(if(FirstTO_Year=Turnover_Year,Value,0))
flipside
Hi flipside,
that works...it was just too easy to find the right way. Thanks...