Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a Targets table comprinsing of the following type of information
UserId | Owner | Financial Week # | Sales Target Value |
---|---|---|---|
63 | Jo Bloggs | 1 | 500.00 |
50 | Sam Smith | 1 | 250.00 |
41 | Jane Doe | 1 | 600.00 |
63 | Jo Bloggs | 2 | 520.00 |
50 | Sam Smith | 2 | 100.00 |
41 | Jane Doe | 2 | 800.00 |
etc
I need to be able to caclulate total targets based on variables i.e:
Sum({$<[Financial Week #] = {'$(vMaxWeek)'},[Year]={'$(vMaxYear)'}>}[Sales Target Value])
This works at present as the Financial week # joins this table in the script to the calendar.
example:
It doesn't take the Owner into account though. I can't join the owner to the Users table (by UserId) without creating loops
Is there something I can add to the expression that means the sum is only calulated when the Owner seleted matches the Owner from the Targets table?
I've looked at creating a link table as a possible option, but can't see what I woudl use to make a key that wouldn't also create a loop..
Any ideas would be most appreciated, thanks in advance...
Heather
Is anyone able to help with this i'm trying to write an expression that includes where Userid = UserID, but it only returns the correct results if you select both UserId and UserID - and I just need it to sum the results where UserId in the target table matches the UserID of the Owner selected.
This works:
Sum({$<UserId = {'138'},[Financial Week #] = {'$(vMaxWeek)'},Year={'$(vMaxYear)'}>}[Sales Target Value])
.... but not when I replace value 138 for field UserID