Straight Table of No Sales for Given Year

[asmithids]

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.

[swuehl · Accepted solution]

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)

[#3]

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

[asmithids]

That worked!

Thank you swuehl

[asmithids]

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,

[#6]

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

Please post sample qvw.

[asmithids]

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!!

[asmithids]

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,