Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Compare dates in Set Analysis

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

1 Solution

Accepted Solutions
Specialist II
Specialist II

I think this works ...

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

flipside

View solution in original post

6 Replies
Specialist III
Specialist III

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.

Partner
Partner

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.

Specialist III
Specialist III

Dear,

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

Thanks,

Mukram

Not applicable

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

Specialist II
Specialist II

I think this works ...

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

flipside

View solution in original post

Partner
Partner

Hi flipside,

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