How to exclude wrong records

[Anonymous]

Hi all,

I have the following problem.

Let's suppose we have the table below, with 3 machines that have worked for 3 months. For each, there are their working hours.

Machine	Month	Working Hours	Validity
SC01	Jan	10	yes
SC01	Feb	12	NO
SC01	Mar	12	yes
SC02	Jan	14	yes
SC02	Feb	12	yes
SC02	Mar	11	yes
SC03	Jan	12	yes
SC03	Feb	10	NO
SC03	Mar	11	yes

Users can select a Month and a Machine from a list box, in order to see the sum of hours. This is very simple.

The problem is that some data are wrong and these mistakes are registered in column Validity. If, in the period selected by a user, a machine has wrong data (i.e. Validity =NO), even just for a single month, all records for that machine must be ignored and the list box of Machine must not display that machine. So I have to consider only records that have count(Validity='yes')=GetSelectedCount(Month).

Some examples:

1) if a user selects Jan, all data are available --> no problem!

2) if a user selects all months, SC01 and SC03 mustn't be available and their working hours mustn't be considered.

Thanks in advance for your help!

Elena

[Anonymous]

Hi,

use in your listboxes instead of field an expression:

=if(not match(Validity, 'NO'), Machine)

in your expressions in the table add set analysis to your formula for example:

sum({< Validity = {yes} >} [Working Hours])

you could also filter the data in script. but this will obviously only work if you dont need not valid machine data in the ui at all.

Best regards

Stefan

[ecolomer]

If you present info in a pivot table simple you have all (I think)

[ecolomer]

And also with totals for validity

[ecolomer]

The qvw file

[Anonymous]

Hi,

You may try below script,

Sum({$<Machine=E({1<Validity={NO}>})>} [Working Hours])

Thanks

[maxgro]

I think you just have to replace 1 with $ because the Machine to exclude depends on the selection

Sum({$<Machine=E({$<Validity={NO}>})>} [Working Hours])

[Anonymous]

Thank for your all suggestions!

@Massimo: your instruction perfectly works with selections on month, but it doesn't work when a machine is selected...

Maybe it's not possible to implement this kind of logic...

[antoniotiman · Accepted solution]

Hi Elena,

try to add Calculated Dimension

Aggr(If(Validity='yes',Machine),Machine)

and Month

See attachement

Regards,

Antonio

[Anonymous]

Thank you Antonio!

It sounds good

Thanks!