topic Re: Sum a Value with most recent reload date in QlikView

Sum a Value with most recent reload date

Wed, 07 Oct 2015 15:54:50 GMT

I am currently reloading data daily, I am trying to Sum a value for the most recent day for the most recent month? What's the best way to add this into set analysis if possible

Re: Sum a Value with most recent reload date

sunny_talwar — Wed, 07 Oct 2015 15:58:13 GMT

May be this:

Sum({<Date = {"$(=Date(Max(Date), 'YourDateFieldFormat'))"}>}Value)

Re: Sum a Value with most recent reload date

Wed, 07 Oct 2015 16:10:39 GMT

Sunny,

Won't this return the last date in the field, which would be Sunday? I would need the last reload date.

Re: Sum a Value with most recent reload date

NickHoff — Wed, 07 Oct 2015 16:23:36 GMT

In your main create a variable such as

LET vLoadDate = Today();

Then create an inline table to read the load date, store this into a .qvd then read this .qvd at a later time in your script.

LoadDate:

LOAD * INLINE [

LoadDate

$(vLoadDate) AS LoadDate

];

STORE LoadDate INTO ..\..\.qvd;

Use set analysis to have your Date = LoadDate Field

Re: Sum a Value with most recent reload date

sunny_talwar — Wed, 07 Oct 2015 16:50:23 GMT

Create a variable like Nick just mentioned:

LET vLoadDate = Date(Today());

and on the front end, may be use this:

Sum({<Date = {"$(vLoadDate)"}>}Value)

Re: Sum a Value with most recent reload date

Clever_Anjos — Wed, 07 Oct 2015 16:56:30 GMT

What about this?

FirstSortedValue(YouValue,-YourDateTimeField)

Re: Sum a Value with most recent reload date

Wed, 07 Oct 2015 16:57:30 GMT

I created the Variable, but this is summing the value across the data set to date. I only want month to date on the last reload date.

Re: Sum a Value with most recent reload date

NickHoff — Wed, 07 Oct 2015 16:59:10 GMT

You need to write the date to a .qvd as I suggested above, otherwise the variable will always read todays date not the actual load date.

Re: Sum a Value with most recent reload date

NickHoff — Wed, 07 Oct 2015 17:04:57 GMT

You didn't mention month to date though, that will change your set analysis to using a current load month indicator in your applications calendar such as:

IF(monthstart(LoadDate) = monthstart(today()),1,0) AS CurrentLoadMonthIND,

Use the following set to get the current load month to date on your load

SUM({$<CurrentLoadMonthIND = {'1'}>}Field)