expression criteria by dates fields only-set analysis

[#1]

hi,

lets say i have a model with 100 fields.

in one of my expression i need to count the days, for example i choose january and year 2011 i get 31.

but i want to count days ignoring any selection in  any fields except my dates field selections.

for example i want to calculate the average sales for a day in january.

so if product sold for 900 pieces in one day in january its should give me 900/31 average sales for a day, even i select the product

( this selection leave me with 1 day from january).

hope its clear.

thanks.

[swuehl]

Hi

I am not completely sure if I understood, but maybe you could try something like

=day(monthend(max( Date)))

This should give you the number of days of the month where the max possible Date is located in. So if you select 2011 and January, even when you limit the possible dates in this month by explicite or implicite selection (e.g. to one remaining), you should get 31.

Hope this helps,

Stefan

[IAMDV]

Hi,

Not sure, if I have fully understood the requirement. However, if you wanted to ignore all the selections except the Date field then you can use the below Set Analysis Expression.

I am assuming Date and Sales as the field names. Ofcourse, please change them based on your requirement. Also, please feel free to add more fields after Date field.

=AVG({$<[$(=Concat({1<$Field-={'Date'}>}distinct $Field,'],[')&']=')>} Sales)

Cheers - DV

[#4]

thanks for all the answers.

ill clear my question:

lets say i have model with 100 fields.

i want to have an expression that is depend on selection of 4 fields of dates like date, month, year, week.

i dont know what will be the selection of the 4 fields but i want to consider it only, and mean time ignore

any selection in all other 96 fields.

hope it clear now.

do.

[swuehl]

Have you tried a set expression like suggested by D V then?

I think another way would be to use set identifier 1 and

sum( {1<date=p(date),month=p(month),year=p(year),week=p(week)>} Value)

Regards,

Stefan

[#6]

Hi stefan,

i think your solution is good ill try in production soon.

thanks,

do

[#7]

hi,

i am sorry , but the use of the p fuction in set analysis is not work since it bring posible values

in the by the selection even the 1 is set in the begining of the set analysis expression.

regards,

doron

[swuehl]

Ah yes, you are right, that's probably not what you want.

Have you tried D V's expression? I think this should be the right approach then.

There might be an equal sign missing after the first ] but in general it should work. It just clears all other fields with <Field1=, Field2=, Field2= > using system fields to retrieve all field names (so in your example, some 99 fields getting cleared).

But maybe I am still not getting all of your requirements, so maybe a small concrete sample might help us.

Regards,

Stefan

[IAMDV]

Hi,

I agree with Stefan. It will be good if you can post a sample QV document.

Meanwhile, have you tried this expression...

=SUM({$<[$(=Concat({1<$Field-={'date', ‘month’, ‘year’, ‘week’}>}distinct $Field,'],[')&']=')>} Value)

This should work as long as you have the correct field names...

Good luck!

Cheers - DV

[SunilChauhan]

we can restrict the selection using

following syntax

Sum({ < field1={$(=only(field1))},field2={'Q1'},Field3=,field4=> } value)

use like above

where field 4 is removed from selection i.e there is no effect of selection of field4 on the expression