Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Problems understanding and using Only(Year)

I have a dataset that includes information for two years, 2011 and 2012.

It is nothing more than a list of weekly production volumes - that is Year, Week, Litres Produced.

The following are selected elements of the data.

2011

2012

Maximum Volume

714,214

644,428

Minimum Volume

887

398,564

Total volume

24,263,544

8,026,290

In an attempt to check it works, I have created a field called OY with the expression Only(Year) and, as I had hoped, this returns 2011 for all the 2011 week lines and 2012 for all the 2012 week lines.

My problem:

I need to show the total volume for the applicable year on each week line (so 2011 total for 2011 week lines and 2012 total for 2012 week lines).

Using Set Analysis - and a lot of trial and error, because this is NOT very intuitive!!! - I have managed  to achieve the following returns for each week line in the appropriate year.

2011

2012

Max({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])

644,428

644,428

Min({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])

398,564

398,564

Sum({$<Year={$(=Max(Year))}>} TOTAL [Litres Produced])

8,026,290

8,026,290

Max({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])

714,214

714,214

Min({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])

887

887

Sum({$<Year={$(=Min(Year))}>} TOTAL [Litres Produced])

24,263,544

24,263,544

However when I try to use Only(Year), I get this…

2011

2012

Max({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])

NULL

NULL

Min({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])

NULL

NULL

Sum({$<Year={$(=Only(Year))}>} TOTAL [Litres Produced])

0

0

So, there is CLEARLY something I do not understand about Only(Year) as this is making no sense to me and driving me up the wall.

Can anyone save my sanity?

Many thanks.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Problems understanding and using Only(Year)

Your set expression and this part: $(=Only(Year))

will be evaluated once per chart, not per dimension value.

So, if you have not selected any Year, only(Year) will return NULL, since you've still got two values and only() function requires a unambiguous value returned.

You could either replace your set expression with an if() clause or use an if() clause to check the year in your expression and then use two set expressions with the year set fixed.

Hope this helps,

Stefan


2 Replies
MVP
MVP

Problems understanding and using Only(Year)

Your set expression and this part: $(=Only(Year))

will be evaluated once per chart, not per dimension value.

So, if you have not selected any Year, only(Year) will return NULL, since you've still got two values and only() function requires a unambiguous value returned.

You could either replace your set expression with an if() clause or use an if() clause to check the year in your expression and then use two set expressions with the year set fixed.

Hope this helps,

Stefan


Not applicable

Problems understanding and using Only(Year)

Thank you Stefan,

That is a seriously big light-bulb moment for me!!!

I stil need to get my brain around the if() clause, but I think I can cope with.

Muchas gracias!!!