Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have used a combo chart showing Budget, Last year and current year values. I don't want to show values for months that haven't happened yet and want the line chart to stop at March. Attached is an image (Chart 1) showing the same. Please let me know how this can achieved and if there is an expression that can be added to the specific measure in order to get the desired result.
Secondly, I have a bar chart where I have added the following expression =if([Year]='2017'or [Year]='2018',[Year]) in dimensions, so that values for only these 2 years and 2018 Budget are shown. However, I see 0 values which keep showing up in between the bars. Attached is the image (Chart 2).
Please let me know how I can fix these issues.
Thanks,
Rony
Hi,
Both should be solvable by unticking the 'show zero values' in the chart's properties.
Hi Lauri,
I tried that but it does not seem to be working. Is there any other way this can be achieved?
Hi,
in order to remove the 0 from the upcoming months where data is not available you can restrickt the Line chart by punting the Set Analysis for the current year YTD Expression or you can particular month should`nt be greater then your current month.
if you have got what you want, i request kindly share all the chart expression with dummy data.
Regards,
Bhasker
Hi Ron,
First Point :
There is data on Y-1 so it shows 0 points indeed..
This is not a good practice but will help you in your issue :
Max Year measure :
if(num(Month)<=count(distinct total {1<Year={$(=max(Year))}>} Month), sum({<Year={$(=max(Year))}>}Amount))
Y-1 measure :
sum({<Year={$(=max(Year)-1)}>}Amount)
Second Point :
You created 2 different measures : one for each year + you added the year dimension
Just drop the Year Dimension from the object
I can only suggest you to learn about Set Analysis : you could save much time using set analysis to filter on year dynamically (not having to change values in filter, or even filter in dimensions, selections on years will still affect y and y-1 measures, etc.)
A good practice is to not use if functions in Qlik objects to filter data, but use set analysis instead :
LivingQlik Roots: The Ultimate QlikView Set Analysis Reference
Regards,
Hi Bhasker,
Can you give the expression which will help me restrict the line chart.
Hi Thomas,
Thank you for your response. I do have to learn more about set analysis. Let me try this and get back to you.
Regards,
Rony
Hi Thomas,
I am still not able to get this right. It is probably because I am not able to read your expression in the correct manner. Would it be possible for you to explain the "Max Year Measure" and "Y-1 measure" so that I can apply it in my data set, accordingly. This is with respect to First Point. Same goes with the Second Point too. Thank you for your help.
Rony
Hi Ron,
Maybe you can share the app (with scrambled/fake data in it) so we can provide you the expected expression ?
sum({<Year={$(=max(Year))}>}Amount)
=> sum(Amount)
where
{<Year={$(=max(Year))}>
==> Year=max year of selection
{<Year={$(=max(Year)-1)}>}
==> Year=max year of selection -1
if(num(Month)<=count(distinct total {1<Year={$(=max(Year))}>} Month), EXPRESSION)
==> if month in your chart is lower than max month of max year on the entire app, whatever the selection, then put the expression, if it is not, do not show nothing
It assumes your month field is a dual type (numeric and string) = but it seems it is as months are properly ordered in your chart
For second chart, did you try to just drop the 'year' dimension ?
Regards,
Thomas
Hi Thomas,
Thank you for the explanation. I will try this and get back to you with scrambled data if it does not work out. Regarding the second chart, what do you mean by dropping the year dimension? What do I add instead?
Regards,
Rony