Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patrickbender
Contributor III
Contributor III

Set Analysis with variable

Hi all,

I have tried to solve what I thought would be a simple task for a while now and I must be missing something.

I have defined two variables:

beforeDate = 2018-10-30

lastDate = MAX({$<dateCol={"<=$(=beforeDate)"}>}dateCol)

In a chart i have no trouble using "=$(lastDate)" in order to get the most current date before "beforeDate".

But when I want to use it in a Set analysis i get into trouble.

This is my current expression:

Sum({$<dateCol={"=$(=lastDate)"}>}costCol)

What am I missing?

13 Replies
patrickbender
Contributor III
Contributor III
Author

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

chrismarlow
Specialist II
Specialist II

Patrick,

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

Cheers,

Chris.

patrickbender
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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.