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: 
ronman10
Creator
Creator

Restrict Line Chart Till the Month where value is available

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,

RonyChart 1.PNGChart 2.PNG

1 Solution

Accepted Solutions
thomaslg_wq
Creator III
Creator III

Hi Ron,

Here you go : find attached the .qvf file.

Regards,

Thomas

View solution in original post

30 Replies
Lauri
Specialist
Specialist

Hi,

Both should be solvable by unticking the 'show zero values' in the chart's properties.

ronman10
Creator
Creator
Author

Hi Lauri,

I tried that but it does not seem to be working. Is there any other way this can be achieved?

bhaskar_sm
Partner - Creator III
Partner - Creator III

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

thomaslg_wq
Creator III
Creator III

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 : 

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/se...

LivingQlik Roots: The Ultimate QlikView Set Analysis Reference

Regards,

ronman10
Creator
Creator
Author

Hi Bhasker,

Can you give the expression which will help me restrict the line chart.

ronman10
Creator
Creator
Author

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

ronman10
Creator
Creator
Author

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

thomaslg_wq
Creator III
Creator III

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

ronman10
Creator
Creator
Author

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