Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mfigueiredo
Contributor III
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
rubenmarin

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

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

rubenmarin

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
Contributor III
Contributor III
Author

Hi Imad,

For some reason the expressions completely disappear...

Thank you very much for your help either way! J

mfigueiredo
Contributor III
Contributor III
Author

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).

rubenmarin

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

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

mfigueiredo
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

rubenmarin

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.