topic Re: Expression and drill down in QlikView

Expression and drill down

Thu, 17 Dec 2015 18:24:45 GMT

Hi all,

I have this expression that looks at average sales for the last 7 days.- which I believe works fine.

=Avg({1<Sale_Date={">=$(=Max(Sale_Date)-7)<=$(=Max(Sale_Date))"},Sale_Branch={'Branch01'}>} Product)

Also, I have two other fields in my data model.

Location Team

Off Shore A

On Shore B

Near Shore C

How can I factor Location and team into the expression, so that when I click e.g Team:A my calculation will show Team:A sales in the last 7 days? I do not want to write the long list of Location and Team directly into my expression.

Thank you.

Re: Expression and drill down

Peter_Cammaert — Thu, 17 Dec 2015 18:29:22 GMT

Remove the 1 set specifier at the front to re-enable selections.

Re: Expression and drill down

Thu, 17 Dec 2015 21:37:23 GMT

I added "1" so that I can show a trend line for the period.

Please is there any other solution apart from this?

Re: Expression and drill down

Peter_Cammaert — Thu, 17 Dec 2015 23:20:11 GMT

Try with adding these to your set modifiers:

Team = {$(=chr(39) & concat(Team, chr(39) & ',' & chr(39)) & chr(39))},

Location = {$(=chr(39) & concat(Location, chr(39) & ',' & chr(39)) & chr(39))}

Off the top of my head, so untested.

Peter

Re: Expression and drill down

Thu, 17 Dec 2015 23:49:02 GMT

Unfortunately, it does not seem to work for me.

Re: Expression and drill down

Thu, 17 Dec 2015 23:54:34 GMT

Do you mean I should use this?

=Avg({1<Sale_Date={">=$(=Max(Sale_Date)-7)<=$(=Max(Sale_Date))"},Sale_Branch={'Branch01'},

Team = {$(=chr(39) & concat(Team, chr(39) & ',' & chr(39)) & chr(39))},

Location = {$(=chr(39) & concat(Location, chr(39) & ',' & chr(39)) & chr(39))}

>} Product)

Re: Expression and drill down

Peter_Cammaert — Thu, 17 Dec 2015 23:57:59 GMT

Yes.

If this doesn't work, can you post an example document?

Re: Expression and drill down

Fri, 18 Dec 2015 14:09:04 GMT

Thanks Peter,

This works now:

Team = {$(=chr(39) & concat(Team, chr(39) & ',' & chr(39)) & chr(39))},

Location = {$(=chr(39) & concat(Location, chr(39) & ',' & chr(39)) & chr(39))}

Regards

Re: Expression and drill down

Peter_Cammaert — Fri, 18 Dec 2015 14:11:42 GMT

My pleasure.

Enjoy QlikView and the Qlik Community.