Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor II

## Line chart with two dimentions do not display correctly

Hi

I need to create a Line chart with the given values into the table:

The Pivot Table works fine, but when I change the view to Line Chart, for some reason the values are not display as the REAL column.

• AmtUSD - Is the full accumulation of the month from the previous month
• DaysOfMonth - Is the full accumulation of the number of dates from the previous month
• Real - The result od divide the value of AmtUSD / DaysOfMonth

I need to create two separate charts

1) With the REAL value of the month

2) With the REAL Full Accumulation from the previous month

1 Solution

Accepted Solutions
MVP

Hi @Barry-Gon, the issue is that line chart is sorted by month-year (instead of the year-month used in pivot), so the Above() will get the previous year, not the previous month.

The count of days can be easily switched using another kind of expression that doesn't uses Above(), like:

=Floor(MonthEnd(MakeDate(Año, Mes_Num)))- YearStart(MakeDate(Año))+1

The amount con be trickier, you can use an as-of calendar to do the accumulations of each month https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

But in this case another solution can be having the calculated value for max year and another for the previous, like:

MontoUSDYTD: =rangesum(above(TOTAL If(Año=Max(TOTAL Año),(sum({ <[Escenario] = {'REAL'},[Concepto01]={'Egresos'} > }MontoUSD ) / vDivision),0),0,RowNo(TOTAL)))

MontoUSDLYTD: =rangesum(above(TOTAL If(Año=Max(TOTAL Año)-1,(sum({ <[Escenario] = {'REAL'},[Concepto01]={'Egresos'} > }MontoUSD ) / vDivision),0),0,RowNo(TOTAL)))

And use an If()in REAL expression to pick one or the other:

=Num((If(Año=Max(TOTAL Año),MontoUSDYTD, MontoUSDLYTD)/DiasDelMes1) , vFormatoInd)

2 Replies
MVP

Hi @Barry-Gon, the issue is that line chart is sorted by month-year (instead of the year-month used in pivot), so the Above() will get the previous year, not the previous month.

The count of days can be easily switched using another kind of expression that doesn't uses Above(), like:

=Floor(MonthEnd(MakeDate(Año, Mes_Num)))- YearStart(MakeDate(Año))+1

The amount con be trickier, you can use an as-of calendar to do the accumulations of each month https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

But in this case another solution can be having the calculated value for max year and another for the previous, like:

MontoUSDYTD: =rangesum(above(TOTAL If(Año=Max(TOTAL Año),(sum({ <[Escenario] = {'REAL'},[Concepto01]={'Egresos'} > }MontoUSD ) / vDivision),0),0,RowNo(TOTAL)))

MontoUSDLYTD: =rangesum(above(TOTAL If(Año=Max(TOTAL Año)-1,(sum({ <[Escenario] = {'REAL'},[Concepto01]={'Egresos'} > }MontoUSD ) / vDivision),0),0,RowNo(TOTAL)))

And use an If()in REAL expression to pick one or the other:

=Num((If(Año=Max(TOTAL Año),MontoUSDYTD, MontoUSDLYTD)/DiasDelMes1) , vFormatoInd)

Contributor II
Author

Thank you @rubenmarin it works perfect for two years. Now I have the question below https://community.qlik.com/t5/New-to-QlikView/Line-Chart-max-year-and-min-year-behavior/m-p/1779487#...