Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
cgbhello
Contributor
Contributor

What is wrong with my line chart?

I tried to create 3 different line charts for 2018, 2019 and 2020 respectively. I have a column, [invoiced_mo], that is formatted like so: YYYY_MM. I want it to be 'MMM-YY' so I created a new field called [MONTH_YEAR] in my script to do so, which did work.

LOAD Date(MonthName(Date#([invoiced_mo], 'YYYY_MM')), 'MMM-YY') as MONTH_YEAR
FROM $(vQVDPathExtract)Vendor_Billing.qvd (qvd);

 

Because [MONTH_YEAR] includes all 3 years, I then separated by the corresponding year as a calculated dimension in each line chart:

=if(subfield(MONTH_YEAR,'-',2)='18',MONTH_YEAR)  

=if(subfield(MONTH_YEAR,'-',2)='19',MONTH_YEAR)

=if(subfield(MONTH_YEAR,'-',2)='20',MONTH_YEAR) 

Then for each of the line charts, I did 2 simple expressions to sum the total costs and total number of requests (e.g. sum(price), count(requests)

 

However, my line charts are showing a straight line across for the price and requests for each year. I'm not sure why the total costs/number of requests are not properly corresponding to the month/year data.

3 Replies
edwin
Specialist III
Specialist III

You may need to provide more info or a QVW at best.  just looking at the pics, a lot of info is missed.

having said that, normally you would limit the YEAR in the expression and not in the dimension.  You should link your fact to a calendar and in the calendar, you determine the year, month, etc..

then in your chart (if you really need a chart just for a specific year), you would have an expression 
SUM({<YEAR={2020}>}Measure)
Your dimension could be MONTH (whatever format)

 

cgbhello
Contributor
Contributor
Author

Thank you for your response! I'm relatively new to qlik (been using it for a little over a month) so I apologize for the silly question I'm about to ask, but could you please explain what all the additional characters mean or do in your expression, SUM({<YEAR={2020}>}Measure)? I created the master calendar per your suggestion and put the month as a dimension. I'm just not understanding how to properly execute the expression line. Thanks!

Brett_Bleess
Support (Former)
Support (Former)

That is Set Analysis, here is a Design Blog post that should be useful as well as Help Doc Link:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

If you wish to search more in the Design Blog area, use this link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.