topic Re: Problem with a set analysis against a disconnected table in QlikView

Problem with a set analysis against a disconnected table

Fri, 04 Dec 2015 20:38:06 GMT

Hi, this is my first message here. First of all, thank a lot for your support. Sorry for my English.

I have a document with a few tables, one of them has all the data that I need to process and this table is linked with others. To make the work easier I copy all the data to a disconnected table and i use this table with a set analysis sentence.

My problem is when I try to do a set analysis against the disconected table the sentence doesn't work. For example:

WorkTable:

LOAD * INLINE

[Date,Dim1,Dim2,Amount

01/01/2010,Software,Software,100

01/02/2010,Software,Software,200

01/03/2010,Software,Software,300

01/01/2011,Software,Software,400

01/01/2012,Software,Software,500

01/02/2012,Software,Software,600

];

DisconnectedTable:

LOAD

Date as DateDisc,

Dim1 as Dim1Disc,

Dim2 as Dim2Disc,

Amount as AmountDisc

FROM resident WorkTable;

In my document I have a Year dimension and when the user choose Year=2012 i want to show the amount between the first date and the date of the dimension but for doing that i need to use the disconnected table because I have other different dimensions:

Month - total amount

01 - 1500

02 - 2100

03 - 2100

I use this expression in a graph with a month dimension: sum({$<DateDisc={"<=$(=makedate(year(Max(Date)),month(Max(Date)),day(Max(Date))))"}>}AmountDisc) but this expression only show the following:

Month - total amount - =Max(Date)

01 - 2100 - 31/01/2012

02 - 2100 - 29/02/2012

03 - 2100 - 31/03/2012

.....

What's the problem with the set analysis? Thanks!!!

Re: Problem with a set analysis against a disconnected table

ecolomer — Fri, 04 Dec 2015 23:00:23 GMT

Try this:

sum({$<DateDisc={"<=$(=(Max(Date))"}>}AmountDisc)

Re: Problem with a set analysis against a disconnected table

Sat, 05 Dec 2015 07:54:57 GMT

I tried but don't work. I get the same result. I think that qv don't apply the month of the dimension to max(date). Always the max(date) returns 31/12/YYYY.

Can i apply to a set expresion the dimension of a table?

Can I get a graph with a month dimension and one set expresion filtering a disconnected table using this dimension?

Thanks!

Re: Problem with a set analysis against a disconnected table

Clever_Anjos — Sat, 05 Dec 2015 11:18:25 GMT

Set analysis expressions are evaluated before the aggregation inside your graph.

If you have a month dimension, Max(Date) would return same value for each row

Re: Problem with a set analysis against a disconnected table

ecolomer — Sat, 05 Dec 2015 13:56:21 GMT

Yes, if you have the month or year in a dimension don't work property and you have always the same value for all row's

Re: Problem with a set analysis against a disconnected table

Sat, 05 Dec 2015 16:46:11 GMT

Ok, i undestand it.

And then, how can i resolve my problem if i can't use sets?

For example with this example data:

Table1:

Load * inline

[Date,group,amount

01/01/2010,Pas,1

01/02/2010,Pas,2

01/03/2010,Pas,3

01/04/2010,Pas,4

01/05/2010,Pas,5

01/06/2010,Pas,6

01/07/2010,Pas,7

01/01/2008,Act,1

01/02/2009,Act,2

01/03/2010,Act,3

01/01/2010,Oth,1

01/02/2010,Oth,2

];

I need something like this, selecting 2010 in year dimension:

Month - (acumulated Pas + acumulated Act)

01 - 1+1=2

02 - (1+2)+(1+2)=6

03 - (1+2+3)+(1+2+3)=12

04 - (1+2+3+4)+(1+2+3)=16

05 - (1+2+3+4+5)+(1+2+3)=21

06 - (1+2+3+4+5+6)+(1+2+3)=27

.....