topic Line chart with two dimentions do not display correctly in QlikView

Line chart with two dimentions do not display correctly

Barry-Gon — Sat, 30 Jan 2021 22:22:31 GMT

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

Please Help

Thanks in advance

Re: Line chart with two dimentions do not display correctly

rubenmarin — Sun, 31 Jan 2021 09:27:10 GMT

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)

Re: Line chart with two dimentions do not display correctly

Barry-Gon — Tue, 02 Feb 2021 18:33:41 GMT

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#M396334

Thanks in advance