Filtering data from an Excel data source

[system]

I have an Excel data source which contain daily productivity targets for multiple pieces of equipment. The spreadsheet contains an ID field specific for each day to allow me to megre this target data with actual daily productivity data.

I wanted to be able to split the total productivity target across a 12 hour period to be able to track how the actual productivity for each hour compares to the required target productivity for that hour (TotalTargetValue/12). I have been able to do this successfully. I now need to be able to return the total target value for certian machines as not all the machiune in the Excel taget spreadsheet work in the same area.

I have tried to use a WHERE clause and the NOT INLIST functions to exclude the pieces of equipment I do not want included in this particular result.

=Sum([DigUnit_Shift_Target] ForEach ( [DigUnit]) Where (Not([DigUnit] InList(“EX8046”;“WL7030”))))

=Sum([ShiftTarget]) Where (Not([DigUnit] InList(“EX8046”;“WL7030”)))

[ShiftTarget] is a Detail variable with an associated dimension linked to the Unique ID used for merging the data.

This results the correct value, but not as a total values, for some reason it assigns individual values to certain hours in my 12 hours split. The total value of these individual values add up to the correct value I am looking for. I don’t understand why it would be assigning these values to individual hours in my hourly split, as there is not obvious relationship.

I have attached a screen shot of the results. I have spend several hours trying to resolve this and I don’t understand why the filtering is not working

Regards,
Nathan

---

NathQLD (BOB member since 2013-06-08)

[system]

I can´t see the screenshot. Can you also post a sample of your data both the spreadsheet and the result set of the other provider ?

---

rgoulart (BOB member since 2011-08-21)

[system]

I am having a problem uploading the snapshot apparently it was too big… Hopefully it will come through this time

---

NathQLD (BOB member since 2013-06-08)

[system]

Hi, I´m pretty sure it has to do with the merged dim´s. Can´t you send the data in an Excel file so I can test it?

---

rgoulart (BOB member since 2011-08-21)

[system]

Here is a copy of the Excel file. I really appreciate your help with this, it is the only thing stopping me from finalising what I am trying to put together here.
Daily and Monthly Targets.xls (727.0 KB)

---

NathQLD (BOB member since 2013-06-08)

[system]

Hi I Created a wid file on both 3.1 and 4.0 version (problem 31. e problem40 ).

Can you please take a look and show me what you did ?
problem 4.0.wid (28.0 KB)
Problem3.1.wid (126.0 KB)

---

rgoulart (BOB member since 2011-08-21)

[system]

I can modify the 3.1 version
I have tried to replicate as much as I can. Some of the columns in the second query are variables I have created, but I have been able to duplicate the results with the exception of the individual values when I try to filter. This may have something to to with the query definition for query 1…?

Problem3.1.wid (128.0 KB)

---

NathQLD (BOB member since 2013-06-08)

[system]

I have resolved this. The solution was to use the IN contex operator

=Sum([ShiftTargets].[DigUnit_Shift_Target]) In ([ShiftTargets].[Calandaroid ID]) Where( Not([ShiftTargets].[DigUnit] InList(“EX8046”;“WL7030”)))

---

NathQLD (BOB member since 2013-06-08)