topic Re: Need help finding error in logic in QlikView

Need help finding error in logic

Mon, 06 Aug 2012 14:26:26 GMT

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

Re: Need help finding error in logic

Mon, 06 Aug 2012 14:49:06 GMT

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.

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:11:39 GMT

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.

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:24:29 GMT

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,

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:34:46 GMT

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.

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:40:21 GMT

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

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:40:28 GMT

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

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:44:03 GMT

Pablo – CustomerID is unique

Re: Need help finding error in logic

Mon, 06 Aug 2012 15:51:03 GMT

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

Re: Need help finding error in logic

Mon, 06 Aug 2012 18:32:56 GMT

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