Use of "If", "Max" and other sums in an expression

[kevinstanfield]

Hi,

I'm struggling combining if, max and other functions to create a comparison of totals for a date (most recent). If the comparison is up/down I want to add an image. This will be easier to explain by the way of example.

Sample data below.

What I'm trying to achieve is to sum Query_Amount by Query_Status for the date raised or date resolved and subtract the Resolved Query Amount from the Open Query Amount. If the sum is -ve the total amount of Open Query Amount has reduced else it has increased.

Example using the data below:

Date Raised 17/02/2016 Query Amount with an Open Status = 6,308.39 For Date Resolved 17/02/2016 Query Amount with Query Status of Resolved = 4368.62. The comparison would be 6,308.39 - 4368.62 = 1,939.77 so the Open Query Status would have increased and therefore an image would be shown representing an increase.

In writing this I'm potentially seeing other complications such as if the max date for Date Raised is different from max date for Date Resolved then there wouldn't be a true comparison. Has anyone got any suggestions on dealing with this. One thought is to take a snapshot of the data every time the data is refreshed (daily) and store the total values for Open and Resolved as a new table in QV but I have no experience of doing this. If there a way to create new values stored in a separate table when loading data from a script?

Thanks in advance - Kevin

Date_Raised	Date_Resolved	Query_Amount	Query_Status
17/02/2016	17/02/2016	514.66	Resolved
17/02/2016	17/02/2016	800	Resolved
17/02/2016		813.76	Open
17/02/2016	17/02/2016	3053.96	Resolved
17/02/2016		455.59	Open
17/02/2016		26.24	Open
17/02/2016		370.64	Open
17/02/2016		69.49	Open
17/02/2016		3554.7	Open
17/02/2016		48	Open
17/02/2016		28.17	Open
17/02/2016		10.02	Open
17/02/2016		13.56	Open
17/02/2016		1264.86	Open
17/02/2016		24	Open
16/02/2016		10	Open
16/02/2016		20	Open
16/02/2016		25	Open
16/02/2016		2345	Open
16/02/2016		765.43	Open

[swuehl]

I would try to create a canonical date in the script:

LOAD

     Date_Raised,

     Date_Resolved,

    If(Status = 'Resolved', Date_Resolved,Date_Raised) as Date,

     Query_Amount,

     Query_Status

FROM ....;

Then create a chart with dimension Date and as expression

=Sum({<Query_Status = {'Open'}>} Query_Amount) - Sum({<Query_Status = {'Resolved'}>} Query_Amount)