Previous year line chart expression in QlikSense that will show accumulated value in combo chart

[vvira1316]

Hi

I'm selecting a year using Filter pane that [Launch Cal Year] field.

I'm getting correct bar chart for selected year. I'm trying to get a line chart for previous year values that will be accumulative for that year. I've AsOfMonth calendar

I want bar chart for selected year (2017) (it is working), and line chart for previous year (2016).

Selected year Bar Chart Expression

=RangeSum(Above(Count(

{<

[Opportunity Record Type]={'Vendor Opportunity'}

,[Launch Cal Year]={"=$(vGetSelectedYear)"}

>}

Distinct [Opportunity Id]

)

,0, RowNo()), AsOfMonth)            

vGetSelectedYear Definition is GetFieldSelections([Launch Cal Year])

vGetPreviousYearFromSelectedYear definition is $(vGetSelectedYear) - 1

2017 Bar chart is correct

2016 Bar Chart for reference. It is showing correct values. I want this values to show up in a line chart when Year 2017 is selected.

BR,

Vijay

I've included sample data file and QVF file.

Message was edited by: VIJAY VIRA

[felipedl]

Hi Vijay,

What's the output when you combine both graphs (one as bar and other as line)?

In my understanding, it should work ok since you have the month dimension on your graph.

I'm not able to open Sense apps right now, since i have on QV installed.

Could you share your load script for testing it on QV please?

Thanks,

Felipe.

[vvira1316]

Hi Felip,

I've attached the script in text file.

Here is the sample of output chart (PN: line chart values are wrong)

Thanks,

Vijay

[felipedl · Accepted solution]

Hi,

What I observed is the following:

I excluded the Dec month because it had some crazy numbers and distorted the graph.

Used the same expression as you, but with a modification on the oportunity record type.

Expression:

=
RangeSum
(
Above
(
Count
(
{<
[Opportunity Record Type]={'VO'},
[Launch Cal Year]={"$(=vGetSelectedYear)"}
>}
Distinct [Opportunity Id]
),
0,
RowNo()
),
AsOfMonth
)

This got me the result of the print.

Seems to be alright, according to your screenshots.

[vvira1316]

Hi Felip,

What is the expression for line? Is year check dynamic or hard coded? Can you please share your QVW file?

I think your line and bar numbers looks fine. Let me have the expression and file so I can check it out on my side.

Thanks,

Vijay

[felipedl]

Hi Vijay,

Everything is as you stated, the variables define the year and previous year and expression is same, with the opportunity code changed to the correspondent field value.

Expression for the line is also the same, only changing the variable set to the previous year.

Attached the QVW.

Hope it helps,

Felipe.

[vvira1316]

HI Felip,

Thanks for sharing the QVW file. I see that you had little change in variable definition by including = sign. Vendor Opportunity to VO was data change for sharing the information here. The December data is skewed data for 2018 but it is working fine between 2017, 2016 and 2015.

I'm not sure why Qlik Sense behaves differently in combo chart. My expressions are working fine in line chart and bar chart respectively.

I tried it several time and it didn't work. Now all of sudden it stared working.

[felipedl]

Hi Vijay, can you mark the anwser that helped you as correct, so others on the community might find it to?

Felipe.