topic Re: Set Analysis with variable in QlikView

Set Analysis with variable

patrickbender — Thu, 11 Oct 2018 15:52:47 GMT

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?

Re: Set Analysis with variable

chrismarlow — Thu, 11 Oct 2018 16:08:16 GMT

Is this the kind of behaviour you are expecting?

If so then I think issues may have spread from date formats (so I had to change to my local format) & possibly some extra '='s signs (although possibly not).

Something I picked up from a book was that in a straight table if you don't override the label you get to see the dollar expanded formula (so you can see to the right of dateCol in straight table above), which may also help.

Regards,

Chris.

Re: Set Analysis with variable

swuehl — Thu, 11 Oct 2018 18:01:15 GMT

In a set expression field modifier,

= {"=..."}

is used for an expression search, something you don't want or need if you want to filter a known date.

What do you see in a chart using

=lastDate

? The date, correctly formatted same way as dateCol?

Then try

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

Besides this, read

Dates in Set Analysis

The Magic of Dollar Expansions

The Little Equals Sign

Re: Set Analysis with variable

patrickbender — Thu, 11 Oct 2018 21:25:35 GMT

Hi Stefan and Chris,

First of all a big thank you for trying to help me out it is much appreciated!

Even though I tried to amnipulate both the date formats and reading up on the very good articles you posted Stefan I can't seem to get it right.

I attatch a testfile and the source data if someone would like to take a look.

https://we.tl/t-VYL8DjAYbl

Re: Set Analysis with variable

patrickbender — Fri, 12 Oct 2018 05:21:20 GMT

Hi Chris,

After reading Stefans articles I do think that an extra $ is needed but I want to use the variable in the expression and not use a static date as you do in the right column and thats where the wierd things start to happen. The filter doesn't seeem to work for me and I end up with the total value instead of the sum of a record that I want.

I have sent an updated set of files below if you would like to take a look at it

Re: Set Analysis with variable

patrickbender — Fri, 12 Oct 2018 05:27:23 GMT

https://we.tl/t-VejtpmidPI

Re: Set Analysis with variable

chrismarlow — Fri, 12 Oct 2018 07:20:10 GMT

Hi Patrick,

I will try to open the files later. Not sure what you mean by static date, adding another date to my toy application & then updating in the text box (beforeDate);

Am I missing something?

Cheers,

Chris.

Re: Set Analysis with variable

patrickbender — Fri, 12 Oct 2018 07:30:15 GMT

Oh i might have been a bit unclear,

what i mean is that it seems that the set expression in the right column of the table is static and thats where I want to use the variable.

I have data that are not updated every month. so one project could have lastDate 2018-08-01 and a second project might have a lastDate of 2018-09-01. so by using the variable to find the first date before a selected date I am hoping to be able to get data from different periods in the same column.

Hopefully it is clearer when opening the files.

My best regards

Re: Set Analysis with variable

swuehl — Fri, 12 Oct 2018 07:51:01 GMT

Patrick, the dollar sign expansion as well as the set analysis is evaluated only once per chart, not per dimension line, if that's what you expect.

So if you use for example project as dimension, the dollar sign expansion won't be re-evaluated per project.

Re: Set Analysis with variable

chrismarlow — Fri, 12 Oct 2018 11:45:57 GMT

Hi Patrick,

We are both on personal edition, so I can't open your file.

However I did have a think about what I think you are trying to do & I now remember why I hate dates in QlikView.

So in a straight table;

max({$<dateCol={"<$(beforeDate)"}>}dateCol)

Will return the max date before the 'beforeDate' for each account.

So in my very simple model I can then return the relevant sums using the below;

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

So you can see for A the date & value for 30th Sep is returned, but B (where there is no data for Sep) returns 31st Aug instead.

My script is really simple & not based on your spreadsheet;

data:

LOAD * INLINE [

Account, dateCol, costCol

A, 31/10/2018, 10

A, 30/09/2018, 8

A, 31/08/2018, 7

B, 31/10/2018, 12

B, 31/08/2018, 11

];

Hope this helps,

Chris.

Re: Set Analysis with variable

patrickbender — Mon, 15 Oct 2018 12:10:37 GMT

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

Re: Set Analysis with variable

chrismarlow — Mon, 15 Oct 2018 12:20:26 GMT

Patrick,

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

Cheers,

Chris.

Re: Set Analysis with variable

patrickbender — Thu, 18 Oct 2018 14:34:52 GMT

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

];

Re: Set Analysis with variable

chrismarlow — Thu, 18 Oct 2018 14:53:33 GMT

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.