Expression and drill down

[#1]

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

                                  D

                                  E

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.

[Peter_Cammaert]

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

[#3]

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

Please is there any other solution apart from this?

[Peter_Cammaert · Accepted solution]

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

[#5]

Unfortunately, it does not seem to work for me.

[#6]

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)

[Peter_Cammaert]

Yes.

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

[#8]

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

[Peter_Cammaert]

My pleasure.

Enjoy QlikView and the Qlik Community.