Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.