topic Re: Only Last Values (by time) on a used-based selection in QlikView

Only Last Values (by time) on a used-based selection

Wed, 15 Mar 2017 09:54:53 GMT

Hi guys,

I have a data set with a structure like:

LOAD * INLINE [

Date, Project, Costs

201601, A, 2

201601, B, 3

201602, A, 5

201603, B, 9

];

I am requested to sum the cost considering only the last cost value by time for each project, considering the selection made by the user.

For instance, if the user selects 201601 + 201602, the proper sum result would be 8 (5 for A, 3 for B, not considering the cost value for A on jan).

Any idea on how to handle this stuff?

Thank you on advance.

Best,

Matteo

Re: Only Last Values (by time) on a used-based selection

sunny_talwar — Wed, 15 Mar 2017 16:28:01 GMT

May be this:

Sum(Aggr(FirstSortedValue(Costs, -Date), Project))

UPDATE: Sample attached

Re: Only Last Values (by time) on a used-based selection

Wed, 15 Mar 2017 18:15:49 GMT

Dear Sunny,

thank you for your kind answer. Your expression is really cool, it is actually good but it seems not to work in my own dashboard.

I am currently using this one:

=sum(aggr(FirstSortedValue(aggr(sum(Costs),Project,Date),-aggr(Date,Project,Date)),Project))

this expression works fine as well, but I am not able to make it work for the previous year. Do you have any suggestion to make?

Many Thanks,

Best

Re: Only Last Values (by time) on a used-based selection

sunny_talwar — Wed, 15 Mar 2017 18:18:37 GMT

Previous Year?? Where did that come from? Is this a new requirement from the original post? Would you be able to update the post and provide the updated desired output to help you better?