Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
mfigueiredo
New Contributor III

Getting expression to display all months for only one year

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.

example.pngThank you so much,

Mariana.

1 Solution

Accepted Solutions

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

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.

View solution in original post

14 Replies
Not applicable

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

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

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)

mfigueiredo
New Contributor III

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

Hi Imad,

For some reason the expressions completely disappear...

Thank you very much for your help either way! J

mfigueiredo
New Contributor III

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

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

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

....

Not applicable

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

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

mfigueiredo
New Contributor III

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

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.

mfigueiredo
New Contributor III

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

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

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

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.

View solution in original post