help with an expression

[Anonymous]

I think I'm overlooking something ... maybe someone can help me get on the track again.

I have a table which looks something like this:

Servicefact.ServiceOrderNo

Servicefact.RowNo

Servicefact.ArticleCode

Servicefact.Article

....

A ServiceOrderNo only has 1 Article (so no different articles)

A ServiceOrderNo zero or more ArticleCode (Parts that are used)

e.g.

ServiceOrderNo	RowNo	ArticleCode	Article
SO1234	1	CR3032	PP01
SO1234	2	IP20102/R	PP01
SO1235	1	CR3032	PP02
SP1236	1	AT212/R2	PP01
SP1237	1	CR3023	PP02
SP1237	2	AT2112/R2	PP02
SP1238	1	CR3023	PP03

I have overviews for:

Article - ArticleCode ... number of ServiceOrderNo.

But now I'm looking for the opposite, but then a little different.

ArticleCode - total number of ServiceOrderNo for Article (on which the ArticleCode is used)

                         also the ServiceOrderNo which have the same Article but not the ArticleCode!!!

e.g.

ArticleCode	NumberOfSO	explanation
CR3032	5	is used in articles PP01/PP02/PP03 -- sum of all article (also where ArticleCode is not used)
IP20102/R	2	is used in article PP01 -- sum PP01 serviceorderno
AT212/R2	4	is used in article PP01/PP02 -- sum of PP01 / PP02 serviceorderno
...	...

Anyone got a good suggestion how to fix this one??

thx in advance!!

Anita

[Michiel_QV_Fan]

Untested:

if(isnull(ArticleCode) = 0 and isnull(ArticleCode) = 0, count(ServiceOrderNo), 0) for counting where article where ArticleCode is used.

if(isnull(ArticleCode) = 0, count({1<Article = p(Article)>}ServiceOrderNo)) for the second one

[Anonymous]

Never used the part:

{1<Article = p(Article)>}

Using this it seems my date selection is not taken in to account (didn't mentioned this in the example, but there is also a date involved).

rgrds Anita

[#4]

What about 1 to $ to use other filters?

{$<Article = p(Article)>}

[#5]

Why not just use an open declaration for the ArticleCode and then the possible for article.

So it would be Count({<ArticleCode = , Article = P(Article)>}ServiceOrderNo).

Also are you trying to use this for a table or just as a one off expression, since the dimensions of the table may mean you need different expression.

[Anonymous]

I have a date selection (e.g. year/month) ...

And the only dimension used is ArticleCode ... but the expression needs the Article count ...

I already tried to use Count({<ArticleCode = , Article = P(Article)>}ServiceOrderNo) but this didn't do the trick.

rgrds

[#7]

If you want to pull the value into a table then it's much easier to just calculate it in the back end.

Just use the group function in your script to get a number, mine looked like this:

NumberOfSO:

Load

Article,

Count(ServiceOrderNo) as NumberOfSO

Resident Data

Group By Article;

Obviously you would need to use whatever resident you are holding the data in. To get the value you are after then just use sum(NumberOfSO).