Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Table A
YEAR | MONTH | PROVIDER |
---|---|---|
2013 | 08 | P0004 |
2013 | 08 | P0003 |
2013 | 08 | P0002 |
2013 | 07 | P0002 |
2013 | 07 | P0001 |
2013 | 06 | P0003 |
SELECTIONS >> YEAR=2013, MONTH=08
I need count the suppliers like this:
ID | DESCRIPTION | AMOUNT |
---|---|---|
1 | NORMAL "P0002" | 1 |
2 | NEW "P0004" | 1 |
3 | REACTIVATED "P0003" | 1 |
4 | LOST "P0001" | 1 |
My problem is how to count the ID 4.
Any Idea?
Hi,
maybe the expression could look like this:
=if(count({$<Month={$(=Max(Month))}>} Provider)>0 and count({$<Month={$(=Max(Month)-1)}>} Provider) > 0,
'Normal Provider',
if(count({$<Month={$(=Max(Month))}>} Provider)>0 and count({$<Month={$(=Max(Month)-1)}>} Provider)<=0 and count({$<Month={$(=Max(Month)-2)}>} Provider)>0,
'Reactivated Provider',
if(count({$<Month={$(=Max(Month)-1)}>} Provider)>=0 and count({$<Month={$(=Max(Month))}>} Provider)<=0,
'Lost Provider',
if(count({$<Month={$(=Max(Month)-1)}>} Provider)<=0 and count({$<Month={$(=Max(Month))}>} Provider)>0,
'New Provider')
Hope this helps.
Maybe like this:
=count( {<Provider= e({<Month ={08}, Year = {2013}>})*p({<Month ={07}, Year = {2013} ) >} DISTINCT Provider)
i.e. using set analysis with implicite field value definitions ( i.e. p() and e() functions).
See attached example
Tks SALTO, but I did that and it did not work.
Use the NullCount() function.
tks G Wassenaar.
I have two questions:
1) When I select another month (ex 05), the provider P0005 in this month is a new provider and not reactivated. How to can resolved that?
2) In my example I have determined the "status" of providers, but in my app "status" is not a field, it is a calculated dimension. Applying the example you did in one dimension?
1. See attached updated qvw.
2. I have no idea what you're trying to ask.
Very Nice!
But, i think create a dimension STATUS can work better, see attached please.
Its possible put your expressions to work in dimension?
I need to better understand how to work with Indirect Set Analysis, is still confusing to me.
Thanks!