topic Re: expression criteria by dates fields only-set analysis in QlikView

expression criteria by dates fields only-set analysis

Fri, 04 Nov 2011 00:58:56 GMT

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.

expression criteria by dates fields only-set analysis

swuehl — Fri, 04 Nov 2011 01:13:57 GMT

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

Re: expression criteria by dates fields only-set analysis

IAMDV — Fri, 04 Nov 2011 15:37:18 GMT

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

expression criteria by dates fields only-set analysis

Fri, 04 Nov 2011 20:46:11 GMT

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.

expression criteria by dates fields only-set analysis

swuehl — Fri, 04 Nov 2011 21:15:21 GMT

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

expression criteria by dates fields only-set analysis

Sat, 05 Nov 2011 20:45:19 GMT

Hi stefan,

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

thanks,

expression criteria by dates fields only-set analysis

Tue, 08 Nov 2011 17:29:45 GMT

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

expression criteria by dates fields only-set analysis

swuehl — Tue, 08 Nov 2011 23:47:50 GMT

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

Re: expression criteria by dates fields only-set analysis

IAMDV — Wed, 09 Nov 2011 12:21:32 GMT

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

expression criteria by dates fields only-set analysis

SunilChauhan — Wed, 09 Nov 2011 12:54:57 GMT

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

Re: expression criteria by dates fields only-set analysis

swuehl — Wed, 09 Nov 2011 22:28:40 GMT

D V,

could you please verify that you don't miss an equal sign? I think that it should be

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

With out that equal sign, the result will be like

=SUM({$<[Field1], [Field2], [Field3], [Field4]= >} Value)

so only the last field will be cleared.

Regards,

Stefan

Re: expression criteria by dates fields only-set analysis

IAMDV — Thu, 10 Nov 2011 11:57:51 GMT

Hi Stefan,

That’s correct! Logically, I need to have the “=” sign. Although it works on my system without the “=” sign. I am using QV9 with SR7.

Thanks - DV

expression criteria by dates fields only-set analysis

Sun, 20 Nov 2011 16:19:41 GMT

hi ,

thanks for all the answers.

the question is more general.

why set analysis is set to find what to choose and doesnt know from what to ignore.

the question is simple - give an expression that is depend on fields field1,field2.......and ignore the rest of the model fields.

for sure my selection in the fields its depened should be dynamic and constant.

hope it clear my question.

expression criteria by dates fields only-set analysis

swuehl — Sun, 20 Nov 2011 16:31:42 GMT

Well, have you tried D Vs expression?

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

Instead of the fields printed bold, you specify the fields you want still to be considered, but want all other fields to be cleared. Instead of "Value", you specify your field you want to sum its values.

It seems that we still miss something important here, so please post a small example and specify what you expect to see if you select specified values on specified fields.

Thanks,

Stefan