topic Re: Getting expression to display all months for only one year in QlikView

Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 11:32:31 GMT

Hi Qlik Community,

I'm yet again requesting your help.

I have a line graph with four expressions (Europe, International, USA, Total) and one dimension (YearMonth).

Expressions are mostly the same except for the markets that I want in each displayable line. In example:

For USA:

Sum({<Year={$(=max(Year)), $(=max(Year)-1)}, MarketCd={'USA'}>}Sales)

What I need is to display all month data for 2014 and be able to choose the months I want for 2015 using list boxes.

I need this is for a report.

What happens is: When I select one or more months, QlikView displays ONLY those months for both years. Which makes sense, I just don't know what to put in the expression to make it lock all 12 months for 2014 only. I tried using IF and using AND to combine expressions, but was unsuccessful.

I also tried this: Sum({<Year={$(=max(Year)), $(=max(Year)-1)}, Month=, MarketCd={'USA'}>}Sales)

But it locks all months for both years instead, ignoring my month selection. Which also makes sense.

What can I do? I attached an image. Please ignore the EUR axis, it is merely an example.

I cannot attach the qvw due to confidentiality.

Thank you so much,

Mariana.

Re: Getting expression to display all months for only one year

Wed, 13 May 2015 11:39:41 GMT

at first glance I would say you need to use the 1 as the default is $ and it forces the current slection.

Sum({$1 <Year={$(=max(Year)), $(=max(Year)-1)}, MarketCd={'USA'}>}Sales)

please try it and let me kow

Re: Getting expression to display all months for only one year

rubenmarin — Wed, 13 May 2015 11:42:01 GMT

Hi Mariana, you can add a numeric YearMonth field, then you can use this field in set analysis, ie:

Sum({<YearMonth={">=$(=Max(Year)-1 & '01')<=$(=Max(Year) & Num(Max(Month), '00'))}, Year=, Month=, MarketCd={'USA'}>}Sales)

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 11:52:56 GMT

Hi Imad,

For some reason the expressions completely disappear...

Thank you very much for your help either way! J

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 11:56:32 GMT

Hi Ruben,

Thank you for your help. Could you be more specific? How can I add YearMonth field as a numeric field?

Does this involve scripting?

I’m not sure I understand, as I am fairly new to QlikView (less than a month).

Re: Getting expression to display all months for only one year

rubenmarin — Wed, 13 May 2015 12:00:10 GMT

Hi Mariana, yes it involves scripting in the table where you're loading Year and Month, in example:

LOAD Year(Date) as as Year,

Month(Date) as Month,

Year(Date) & Num(Month(Date), '00') as YearMonth

....

Re: Getting expression to display all months for only one year

Wed, 13 May 2015 12:02:31 GMT

Sorry Mariana, That was my initial suggestion before going any further

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 12:06:09 GMT

Hi Ruben,

I understand. The problem is I was told not to script anything since that is the responsibility of the IT department.

I’m only a report/chart developer. I thought I could achieve this via set analysis only.

My script table has only my data model loaded and some date/time formats.

I could ask the IT to do that.

Meanwhile I hope someone could help me do this via set analysis.

Thank you so much.

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 12:06:45 GMT

I’m just happy you tried to help me. Thank you.

Re: Getting expression to display all months for only one year

rubenmarin — Wed, 13 May 2015 12:51:43 GMT

I think I can make it work if you have a field that uses both fields, year and month, can be the commented YearMonth field or simply a Date field.

Otherwise maybe it can be done using if inside the sum, ie:

Sum({<Year={$(=max(Year)), $(=max(Year)-1)}, Month, MarketCd={'USA'}>} If(Year=Max(TOTAL Year)-1 or (Year=Max(TOTAL Year) and Month<=Max(TOTAL Month)), Sales))

Not tested, maybe doesn't works.

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 13:14:33 GMT

I do.

I have a field with YearMonth together in my model. That is what I’m using as this charts’s dimension.

I will try your expression also. Thanks so much!

Re: Getting expression to display all months for only one year

mfigueiredo — Wed, 13 May 2015 13:22:21 GMT

You are a genius! Your expression is perfect.

Thank you a thousand times.

Mariana.

Re: Getting expression to display all months for only one year

rubenmarin — Wed, 13 May 2015 13:26:48 GMT

Nice, try first the set analisys expression, as it's better for performance, it can be simplified a bit:

Sum({<YearMonth={">=$(=Max(Year)-1 & '01')<=$(=Max(YearMonth))"}, Year=, Month=, MarketCd={'USA'}>}Sales)

Change YearMonth using your Dimension field name. I didn't tested so maybe needs debug.

Re: Getting expression to display all months for only one year

rubenmarin — Wed, 13 May 2015 13:27:55 GMT

Good, glad to help!

Re: Getting expression to display all months for only one year

Wed, 13 May 2015 13:29:14 GMT

well done Rubin