topic Re: set analysis 1-$ in QlikView

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 08:57:57 GMT

Hi to all,

aggr ( sum ( { 1-$ <Dataven={">=$(=timestamp(div1)) <=$(=timestamp(dfv1))"}>} QtaTot ) , Id_Art )

formula considerate 1 ( all database) and not 1-$

any idea?

Thank

set analysis 1-$

SunilChauhan — Thu, 08 Sep 2011 09:10:37 GMT

try using this

aggr ( sum ( { {1-$} <Dataven={">=$(=timestamp(div1)) <=$(=timestamp(dfv1))"}>} QtaTot ) , Id_Art )

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 09:15:58 GMT

not work .... and give me sintax error

Re: set analysis 1-$

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 09:42:05 GMT

Hi,

Are you using that in a pivot table or a text object? Did you check that the expression without the aggregation returns the expected value?

Sum({1 - $< Dataven = {">=$(=timestamp(div1))<=$(=timestamp(dfv1))"} >} QtaTot)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 09:59:45 GMT

Hi,

Yes i am using in a pivot table and without aggr it not works as well

Re: set analysis 1-$

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 10:14:11 GMT

Hi,

Then the content of the set modifier is what is causing the issue. Since syntax seems to be fine, the question is has field Dataven the same exact format returned by the TimeStamp() function? Are "div1" and "dfv1" fields or variables? What does the TimeStamp() function return?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 10:33:01 GMT

Hi ,

dataven is made in script with makedate

div1 and dfv1 are 2 variabiles and i used them in calendar object

and timestamp return the same value format

Re: set analysis 1-$

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 11:31:16 GMT

Well, that's not correct. By default, TimeStamp() returns date and time, while MakeDate() returns only the date part.

A very simple check is creating a new text object and write in it

=Match(TimeStamp(40400), MakeDate(2010, 8, 10))

Both 40400 (which I assume is what the variable is storing) and 10/08/2010 (which I assume is what the field stores) refer to the same, the former in QlikView's numeric date format, the second in 'DD/MM/YYYY' string format. Obviously, the result of the expression above is "0" meaning "no match". "10/08/2010 0:00:00" is not equal to "10/08/2010".

However, representing the result of the date part from the TimeStamp() into a text will return a "1" (match)

=Match(Text(Date(TimeStamp(40400))), MakeDate(2010, 8, 10))

You need to be very careful with the set analysis expressions, quoting and formats. Going one step easier, the TimeStamp() function is no longer needed, but it keeps matching:

=Match(Date(40400), MakeDate(2010, 8, 10))

Which applied to your expression should look like

Sum({1 - $< Dataven = {">=$(=Date(div1))<=$(=Date(dfv1))"} >} QtaTot)

And that should return the aggregated sum of the values in field QtaTot except those where Dataven is between the range selected. If the above still doesn't work means that formats are still different, and you will need to use the date functions in both the script for the field Dataven and the variables in the set modifier to make them alike. Starting from here, the Aggr() should work as well.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 12:15:46 GMT

Hi Miguel , i have post little projet ... so u can see wath happen.

data base is very little only 4 rows so easy to test

thank

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 15:18:25 GMT

formula works with

=aggr ( sum ( { 1 <Dataven={">=$(=timestamp(div1)) <=$(=timestamp(dfv1))"}>} q ) , a )

and not with 1-$....

don't understand wath i miss

Re: set analysis 1-$

Miguel_Angel_Baeyens — Thu, 08 Sep 2011 15:27:04 GMT

Hi,

I've checked your document and it's working fine. Total possible sum of q is 100, for the month of February, total sum of q is 20, and 1-$ equals to 80. What results do you expect here?

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Attached is the checked application

set analysis 1-$

Anonymous — Thu, 08 Sep 2011 15:38:31 GMT

Ciao,

i select month 4 and sum of q is 40 and is correct

if i select from calendr from 01/01/2011 to 31/01/2011 sum of q is 10 for each month i put in select

and this is wath i wanted ... the sum of q for month in the select , and the sum of q in different date (div1 and dfv1)

and this work with

=aggr ( sum ( { 1 <Dataven={">=$(=timestamp(div1)) <=$(=timestamp(dfv1))"}>} q ) , a )

and not with 1-$ and don' understand why .. may be i missing something understanding in set analysys???

Re: set analysis 1-$

Miguel_Angel_Baeyens — Fri, 09 Sep 2011 07:31:34 GMT

Hi,

You need to make coherent selections. If you select month 4 in field "m" (according to the file attached above), you need to select variables 1/4/2011 to 30/4/2011, otherwise, the selection done in the set modifier doesn't make sense with the current selection. Note that this is like using a lot of listboxes and try to select m = 4 and Dataven = 1/2/2011, you cannot, you select either one or the other, not both, because they are exclusive.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

set analysis 1-$

Anonymous — Fri, 09 Sep 2011 12:20:10 GMT

hi Miguel , yes u are right .. now i have understand difference between 1 and 1-$ in the set analysys. Thank you very very much for your help.