topic Re: Min and Max Date Dimensionality? in QlikView

Min and Max Date Dimensionality?

Wed, 27 Sep 2017 14:27:48 GMT

First time poster so forgive my explanation.

Background:

I work at an insurance company and I would like to build a viz (QlikView) on claims loss amounts growth/decay. My idea is to have a scatter chart that plots a claim by loss amount (Y) and transaction date (X) twice. One dot be the first transaction connected to the second dot to be the last transaction on the claim. I'm having trouble building out the logic to plot the two points.

Question:

How can I plot a claim on its initial loss amount on its first transaction date as well as plot its total loss amount on its final transaction date? Adding the connecting line should be simple enough by adding Claim Number as the second dimension on the scatter (correct me if I'm under-thinking it).

Bonus question:

Can I conditionally format the color of the connecting line based on the slope of the line (red for sizable positive slope, green for a sizable negative slope and yellow for a flat-ish slope)?

Thank you in advance for any consideration.

Re: Min and Max Date Dimensionality?

vishsaggi — Wed, 27 Sep 2017 15:52:24 GMT

Can you share some sample data would be easy to look into and try?

Re: Min and Max Date Dimensionality?

Wed, 27 Sep 2017 16:08:20 GMT

Sample data, applying the same concept to CustomerID and Sales over YYYYMM.

Re: Min and Max Date Dimensionality?

Wed, 27 Sep 2017 16:22:22 GMT

Not sure if you get notified when I reply to myself.

Re: Min and Max Date Dimensionality?

rwunderlich — Thu, 28 Sep 2017 00:18:02 GMT

That's a pretty interesting idea. Attached is a rough cut, see what you can make of it.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Min and Max Date Dimensionality?

Thu, 28 Sep 2017 14:05:02 GMT

This looks awesome! Thanks Rob.

I don't think the max trandate is calculating sales cumulatively though. It is only providing the sale amount for the latest transaction.

Re: Min and Max Date Dimensionality?

rwunderlich — Thu, 28 Sep 2017 16:18:05 GMT

I just guessed at what you wanted. You can modify the script to sum the Sales like in the attached.

-Rob