topic Re: Plot 2 different expressions to 2 different dimensions on same chart in QlikView

Plot 2 different expressions to 2 different dimensions on same chart

Brock — Wed, 25 Nov 2020 16:16:04 GMT

I have 2 date fields and 2 dollar value fields in my data. Prior Date / Current Date, and Prior Dollars/ Current Dollars over 4 years. The date fields have similar values except for the min and max values. Prior date contains data for 2015-2017, and Current date contains data for 2016-2018. I want to be able to display both on the same chart so that all dates are shown with their proper Prior or Current Dollar value. See in the pic below, the red line should be shifted to the left one year so that the two lines line up and create a line for the full 2015-2018 range. How do I disassociate the two expressions so they are plotted against the separate dimensions? Very new to qlikview. I hope this makes sense.

Re: Plot 2 different expressions to 2 different dimensions on same chart

dplr-rn — Wed, 23 Jan 2019 20:18:40 GMT

Can you share the expressions that you have used

Re: Plot 2 different expressions to 2 different dimensions on same chart

Brock — Wed, 23 Jan 2019 20:29:20 GMT

First is Sum([Current Expense]), second is Sum([Prior Expense]), the dimensions are just the two date fields.

Re: Plot 2 different expressions to 2 different dimensions on same chart

dplr-rn — Wed, 23 Jan 2019 20:31:44 GMT

How is the data structured?
can you share a sample

Re: Plot 2 different expressions to 2 different dimensions on same chart

Brock — Wed, 23 Jan 2019 21:10:26 GMT

The simplest way to show is like this:

ID	ClaimID	Current Date	Prior Date	Current Expense	Prior Expense
1	111	2016	2015	$1000	$500
2	111	2017	2016	$2500	$1000
3	111	2018	2017	$3500	$2500

Each claimID can have multiple records. Each record has current/prior values. When a new record is created for a claimID, the current value moves into the prior position and the next year and expense value are input. The solution I described above would be a workaround. But if you have a better way to get the two "end values" (2015 and 2018) to line up on the same chart I'd love to hear it.

Re: Plot 2 different expressions to 2 different dimensions on same chart

qliksus — Fri, 25 Jan 2019 04:57:13 GMT

May be something like this

claims:
CrossTable(values,data,2)
LOAD * INLINE [
    Current, Previous, Current$, Previous$
    2015, 2014, 10, 12
    2016, 2015, 22, 10
    2017, 2016, 33, 22
];

load
if ( values='Previous$', Previous ,Current) as Dates,
Current,
Previous,
values,
data

Resident claims ;

In the chart use the below

Dimension: Dates

Expression:

Previous: aggr(sum({<values={'Previous$'}>}data) ,Current)

Current: sum({<values={'Previous$'},Current={$(=min(Current))}>}data)+ sum({<values={'Current$'}>}data)