Set Analysis with a varied Date according to each dimension

[#1]

Hi,

My data source is a flat table with the following fields:

    EmployeeName

    TrainingDate: Date that the employee received training, under the format: dd-MMM-yy

    ReportingMonth: Last day of the month, under the format: MMM-yyyy

    CarSold: Number of cars sold within that month

    ...(other fields irrelevant to this question)

Flat table looks somewhat like this:

ReportingMonth	EmployeeName	CarSold	TrainingDate	other irrelevant fields
Dec-2016	A	6	11-Jun-15
Dec-2016	B	8	25-Sep-16
Dec-2016	C	7	5-Dec-16
Dec-2016	D	8	30-Aug-16
Dec-2016	E	6	8-Oct-15
Nov-2016	A	3	11-Jun-15
Nov-2016	B	2	25-Sep-16
Nov-2016	C	4	5-Dec-16
Nov-2016	D	3	30-Aug-16
Nov-2016	E	2	8-Oct-15

I want to create a table in QlikSense that show the total number of car sold by each Employee during the month of his/her training. So far my table is like this:

EmployeeName	TrainingDate	Training Month	Car Sold in Training Month
A	11-Jun-15	Jun-2015	...
B	25-Sep-16	Sep-2016	...
C	5-Dec-16	Dec-2016	...
D	30-Aug-16	Aug-2016	...
E	8-Oct-15	Oct-2015	...

Formula for each column:

    Dimension: =EmployeeName

    Measure 1: =TrainingDate

    Measure 2: =Date(TrainingDate,'MMM-yyyy')

    Measure 3: =sum({<[Other Fields] = {'Other Field values'}, ReportingMonth = {'$(=Date(TrainingDate,'MMM-yyyy'))'}>} CarSold)

I could not get the Measure 3 column to work. How should I write this formula differently?

If this is too much for set analysis, I probably can get rid of the Date(...) formula by changing the format of the TrainingDate to match the ReportingMonth right from the loading script. I'm more concerned of how to get the ReportingMonth to vary with the Dimension.

Thanks a lot! Please let me know if you have any question.

[#2]

I stripped my formula to the bare minimum, essentially this:

=sum({<ReportingMonth>} if(monthstart(ReportingMonth) =monthstart(TrainingDate), CarSold))

And it still doesn't work if I'm keeping the current selection to ReportingMonth='Dec-2016'.

What it shows is: For Employee trained in Dec-2016, it sums all CarSold in all the months

                          For Employee trained on a different month, it returns 0

If I change my selection to ReportingMonth='Jul-2016',

What it shows is: For Employee trained in Jul-2016, it sums all CarSold in all the months

                          For Employee trained on a different month, it returns 0

Again, all is fine if I cancel the ReportingMonth selection.

Thanks! Really appreciate your help.