topic Re: Compare dates in Set Analysis in QlikView

Compare dates in Set Analysis

catunoho — Mon, 12 Jan 2015 12:39:18 GMT

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

Re: Compare dates in Set Analysis

mdmukramali — Mon, 12 Jan 2015 12:48:23 GMT

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.

Re: Compare dates in Set Analysis

catunoho — Mon, 12 Jan 2015 12:55:56 GMT

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.

Re: Compare dates in Set Analysis

mdmukramali — Mon, 12 Jan 2015 13:02:56 GMT

Dear,

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

Thanks,

Mukram

Re: Compare dates in Set Analysis

Mon, 12 Jan 2015 14:26:26 GMT

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

Re: Compare dates in Set Analysis

flipside — Mon, 12 Jan 2015 15:01:44 GMT

I think this works ...

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

flipside

Re: Compare dates in Set Analysis

catunoho — Mon, 12 Jan 2015 15:30:06 GMT

Hi flipside,

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