Counting Items that have positive revenue associated with it
Hello Community,
A very new Qlikview user here, so please be patient with me.
I have a certain number of items/products (ItemNumber) under a Theme and I'm looking for a count of those items, however, I keep getting items that have 0 dollars associated with them that I don't want counted.
Below is the expression I'm using that give me a count over a Trailing Twelve Month (TTM) timeframe that gives me the correct count of items but includes items that didn't generate revenue which I don't want. I tried to add something to say if the sum was greater than 0, but it didn't work.
count({<CalendarWrittenDate={'>=$(=AddMonths((MonthStart(Today())),-12))<=$(=MonthEnd(AddMonths((Monthend(Today())),-1)))'}>}DISTINCT IF (RevenueField>0,ItemNumber) )
sum( {$<Customer = {“=Sum({1<Year = {2007}>} Sales ) > 1000000”}>} Sales ) returns the sales for current selection, but with a new selection in the “Customer” field: only customers who during 2007 had a total sales of more than 1000000.
I'm having a problem adding your suggestion to my expression. I did get the other to work, but now I thought I could use your suggestion because I'm now to get a count of the products/ItemNumber that have generated over $25,000 in revenue over the TTM time period.
Can you still assist?
Thanks,S. Berhannan
Date: Tue, 6 May 2014 16:02:33 -0400
From: qcwebmaster@qlik.com
To: stevenbatl@hotmail.com
Subject: Re: - Counting Items that have positive revenue associated with it
Qlik Community
Counting Items that have positive revenue associated with it
reply from Massimo Grossi in New to QlikView - View the full discussion
try to add to your expression
ItemNumber= {“=Sum (Revenue ) > 0”}
this is an example extracted from online help
sum( {$} Sales )
returns the sales for current selection, but with a new selection in the “Customer” field: only customers who during 2007 had a total sales of more than 1000000.
Reply to this message by replying to this email, or go to the message on Qlik Community
Start a new discussion in New to QlikView by email or at Qlik Community
Following Counting Items that have positive revenue associated with it in these streams:
and Massimo's answers works too if you want to do via Set Analysis.