Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
First is Sum([Current Expense]), second is Sum([Prior Expense]), the dimensions are just the two date fields.
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.
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)