Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have several tables that show Total Revenue (sum of RevDollars) for a certain year (Year) by the year a contract was signed (StartDate). I can't find anything wrong with my expressions, but they aren't pulling correctly, so I am obviously missing something. I have attached a sample file - any help you can give me would be greatly appreciated. I am sure the problem probably lies within the null StartDate values, which I am including as older than 2007, but I can't find where the problem is.
Thanks!!
Hello Rebecca
The problem is that you are duplicating data I think
Try a resident load from your first table using a left join
I cannot give you a working example because I haven't got your excel spreadsheet
Table1:
LOAD CustomerID,
StartDate,
RevDollars,
SalesRep,
Year,
CustomerID&StartDate as Key
FROM
C:\Users\rcamper\Desktop\Book1.xlsx
(ooxml, embedded labels);
Left Join (Table1)
LOAD Key,
if(IsNull(StartDate)=-1,'Y','N') as StartYearIndicator
Resident Table1;
Regards,
Hi,
Start by creating a key and not using the synthetic ones (this is standard practice), this will allow you to check how your data is hanging together. I would say it may not be summing the null years.
Do you know how I would avoid the synthetic key in this instance? The synthetic key is being caused by the IF formula I have included in my script, but I don’t know how to avoid this.
Hello Rebecca
The problem is that you are duplicating data I think
Try a resident load from your first table using a left join
I cannot give you a working example because I haven't got your excel spreadsheet
Table1:
LOAD CustomerID,
StartDate,
RevDollars,
SalesRep,
Year,
CustomerID&StartDate as Key
FROM
C:\Users\rcamper\Desktop\Book1.xlsx
(ooxml, embedded labels);
Left Join (Table1)
LOAD Key,
if(IsNull(StartDate)=-1,'Y','N') as StartYearIndicator
Resident Table1;
Regards,
That cleaned up the Synthetic Keys, but it still looks like it may be duplicating data as you suggested. Here is the updated version with the script you provided. I will upload the Excel spreadsheet also in case that helps.
Now the Totals are matching, but the sum of the year expressions are not matching... Any ideas?
Hello Rebecca
Thay key I assumed as unique may be the problem. Please let me know which combination does not repeat on your base table and use that as key
I will browse through the data and see if I can identify it myself, but if you already know it will help
Regards
Pablo – CustomerID is unique
I see that the expressions have this 2 modifiers, start date and year
sum({$<Year={2010},StartDate={'2010'}>}RevDollars)
This will bring the revenue of 2010 for the ones starting in 2010, is that correct?
If you only want revenue for customers starting in 2010 maybe you should use
sum({$<StartDate={'2010'}>}RevDollars)
If you want to disregard the selections you can try this
sum({1<Year={2010},StartDate={'2010'}>}RevDollars)
Hope it helps
I found my mistake and, of course, it was ridiculously obvious. Your solution above was correct. Thank you for your help!!