Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
asmithbi
Contributor II

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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)

7 Replies
MVP
MVP

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)

Not applicable

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.

asmithbi
Contributor II

Re: Straight Table of No Sales for Given Year

That worked!

Thank you swuehl

asmithbi
Contributor II

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,

Not applicable

Re: Straight Table of No Sales for Given Year

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

Please post sample qvw.

asmithbi
Contributor II

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

Screen Shot 2014-02-18 at 5.16.01 AM.png

asmithbi
Contributor II

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, 

Community Browser