set analysis 1-$

[Anonymous]

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

[SunilChauhan]

try using this

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

[Anonymous]

hi

not work .... and give me sintax error

[Miguel_Angel_Baeyens]

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

[Anonymous]

Hi,

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

[Miguel_Angel_Baeyens]

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

[Anonymous]

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

[Miguel_Angel_Baeyens]

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

[Anonymous]

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

[Anonymous]

formula works with

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

and not with 1-$....

don't understand wath i miss