7 Replies Latest reply: Feb 20, 2014 1:07 PM by Alec Smith

# Straight Table of No Sales for Given Year

Hello,

I built a straight table that only shows customers who have NOT purchased any products based on the selected list box year.  I added the following expression to exclude the current year selected.

=Only({1-\$}Year)

I am getting the expected list of customers who have not purchased any products for the selected year.  However, the issue I am having is that I want to add an expression that will show the average potential sales (i.e average lost sales) for the selected year, but I cannot get the sum calculation to work. I tried the following expression to no success.  I'm not getting any numbers at all.

=Sum((=Only({1-\$}Year)> "Sales Amount") / (Max(=Only({1-\$}Year) - Min(=Only({1-\$}Year))

Thank you in advance for any assistance.

• ###### Re: Straight Table of No Sales for Given Year

only() will probably not work if there are more than 1 year to look at (for example, there are 3 years in your data model, you select the first year and expect the last two years to be returned, but only() can only return a single, unambiguous value).

You can use the implicit field value definitions using the p() and namely the e() function, so maybe try something like

=sum({<Customer = e(), Year= >} [Sales Amount]) / count({1-\$} DISTINCT Year)

• ###### Re: Straight Table of No Sales for Given Year

Only function return if the filed have more than one value. So use swuehl suggested expression.

• ###### Re: Straight Table of No Sales for Given Year

That worked!

Thank you swuehl

• ###### Re: Straight Table of No Sales for Given Year

Thank you swuehl and dathu for your help.

I've since added a Ship To State list box to further filter my straight table.  The issue is that when I filter on a particular Ship To State after selecting my primary Year filter (=Only({1-\$}Year) ), the Year filter is no longer enforced producing a filtered straight table that doesn't show expected results of (=Only({1-\$}Year) AND State.  Actually, the results don't make any sense all.

Thank you,

• ###### Re: Straight Table of No Sales for Given Year

Hi Smith, In which place, are you using this expression:  only({1-\$} Year)

• ###### Re: Re: Straight Table of No Sales for Given Year

Hi dathu,

I attached a sample qvw.  Also, please see image of location of the only({1-\$} Year) expression.  I'm trying to have the only({1-\$} Year) as the primary filter and then add additional filters for City, State, and Zip Code.  I tired using Altered States for this particular sheet, but it did not work.

Thank you!!

• ###### Re: Straight Table of No Sales for Given Year

Hi dathu,

Thank you for your help.  I'm new to QlikView and learning a great deal.  Interaction with the Community has been a tremendous resource.  Everyone here is extremely helpful and willing to offer assistance.  I will "pay it forward" to the Community as I become proficient.

Have you had the chance to review the qvw I posted?  I will understand if you don't have the availability at this time.

Thanks,