Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help finding error in logic

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!!

1 Solution

Accepted Solutions
Not applicable
Author

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,

View solution in original post

9 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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,

Not applicable
Author

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.

Not applicable
Author

Now the Totals are matching, but the sum of the year expressions are not matching... Any ideas?

Not applicable
Author

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

Not applicable
Author

Pablo – CustomerID is unique

Not applicable
Author

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

Not applicable
Author

I found my mistake and, of course, it was ridiculously obvious. Your solution above was correct. Thank you for your help!!