Cumulative

[NiTo]

Hi All,

Cheerzz..

I need to find the difference between last row and the first row wrt DATE, since one field in the table has cumulative value.

Field with cumulative value: conf_case, wrt to submission_date.

thanks

[stevedark]

Hi @NiTo 

Suggest taking a look at this blog post about how you can accumulate values by building a table that links to the existing date field in the data model:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Using this technique you can then use set analysis to pick out the dates that you want to compare, with syntax like this:

sum({<[Accumulate Date]={"$(=date(max([Accumulate Date]), 'DD MMM YYYY'))"}>}Value) - sum({<[Accumulate Date]={"$(=date(min([Accumulate Date]), 'DD MMM YYYY'))"}>}Value)

Or, without using the accumulate date technique, you could simply use set analysis to compare day one with the value for all time:

sum(Value) - sum({<Date={"$(=date(min(Date), 'DD MMM YYYY'))"}>}Value)

You will of course need to amend the field names and date formats to match what you have in your data model.

Hope that helps point you in the right direction.

Steve

[stevedark]

@NiTo 

BTW, does your source data have the cumulative values in it?

If so, you will want to work out the deltas between each day during the load, or any time you try and calculate anything you will double count.

You will need to do this using the peek function, something like this:

tmpData:
LOAD
    *
FROM DataSource;

Data:
LOAD
   *,
   tot_cases - if(State = peek(State, -1), peek(tot_cases, -1), 0) as tot_cases_delta,
   conf_cases - if(State = peek(State, -1), peek(conf_cases, -1), 0) as conf_cases_delta
RESIDENT tmpData
ORDER BY State, submission_date ASC;

DROP TABLE tmpData;

I can't see if you have any other dimension fields other than State that would need to be taken into account when deciding whether the previous line is being accumulated from, but wherever state appears in that code you wil need to list all of the fields.

Hope that helps also.

Steve

[NiTo]

Hi Steve,

Thanks for your reply..