Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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)
Hi Imad,
For some reason the expressions completely disappear...
Thank you very much for your help either way! J
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).
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
....
Sorry Mariana, That was my initial suggestion before going any further
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.
I’m just happy you tried to help me. Thank you.
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.