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]

I'm sorry... just another question.

The calculated dimension Aggr(if(Validity='yes', Machine), Machine) works perfectely with table and listbox.

But, if I had also a text object with the expression sum([Working Hours]), how could I implement that logic?

Is it possible?

[er_mohit]

Try this

Aggr(sum(if(Validity='yes', [Working Hours])), Machine)

[antoniotiman]

See attachement

[jyothish8807]

Hi elena,

Try this:

sum({<Machine={"$(=Aggr(If(Validity='yes',Machine),Machine))"}>}WH)

Regards

KC

[Anonymous]

Thank you very much!

Antonio's and Jyothish Kc's expressions have same results but they don't work properly if I select Jan and/or Mar (because, maybe, the "Aggr" function returns more than one Machine, so set analysis doesn't work).

[antoniotiman]

New attachment

[Anonymous]

Thank you very much, Antonio! You are a magician!

I didn't think it was possible to implent this type of logic.

Thanks!

[Anonymous]

Hello!

Using the correct expression suggested above, Aggr(if(Validity='yes', Machine), Machine), in a list box for Machine (in order to see only those with correct data), when a user makes a selection, the other possible values disappear

Is there a way in order the list box to behave as a common list box, i.e. to show all possible values in gray and the selected ones in green?

Thank you very much!

Elena

[antoniotiman]

Hi Elena,

I think this is not possible. However You can use List box with Field 'Machine' and

expression like this

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

or instead of 'True' use Chr(9733) or other.

See attachment.

Regards,

Antonio

[Anonymous]

Thank you! It's a very nice solution, I lIke it very much.

Thanks!

Elena