Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

patrickbender
New Contributor III

Re: Set Analysis with variable

This worked perfectly!!!

Thank you

Now I just need to read up on the aggr function since i'm not comfterble with the use of that just yet

Highlighted
chrismarlow
Valued Contributor

Re: Set Analysis with variable

Patrick,

Glad it was helpful. I normally end up re-reading the aggr definition every time I use it .

Cheers,

Chris.

patrickbender
New Contributor III

Re: Set Analysis with variable

Hi Chris,

Sorry for opening this thread again.

I thought that it worked out turns out it didn't and when I rebuild your application I understand why as Stefan pointed it out for me. FirstSorted value is not able to sum two values on the same date so when I add a second line with the same date I get 0. Is there any other way to get around this?

I need to get the result 17 for Account A if using this data.

Data:

LOAD * INLINE [

    Account, dateCol, costCol

    A, 2018-10-31, 10

    A, 2018-09-30, 8

    A, 2018-09-30, 9

    A, 2018-08-31, 7

    B, 2018-10-31, 12

    B, 2018-08-31, 11

];

chrismarlow
Valued Contributor

Re: Set Analysis with variable

Patrick,

No worries. I am not sure why the following works (or seems to), but give it a go;

FirstSortedValue({$<dateCol={"<$(beforeDate)"}>} distinct aggr(sum({$<dateCol={"<$(beforeDate)"}>}costCol), Account, dateCol),-dateCol)

I would have assumed the distinct would have applied to the value (the aggregation) and therefor be irrelevant (it is aggregating, after all), but seems like it also has an impact on the sort weight (-dateCol).

Cheers,

Chris.