Discussion Board for collaboration related to QlikView App Development.
I need a little help finding current, new and lost customers. The first part of the script finding old customer and new customers seems to work, but the lost customers I am having a hard time with. Any ideas?
LEFT JOIN (VBRK_R) LOAD [Billing Date], FISYR RESIDENT FiscalCalendar; LEFT JOIN (VBRP_R) LOAD %BillingDocument_Key, [Billing Date], FISYR RESIDENT VBRK_R; Old_Customers: LEFT JOIN (KNA1_NEW) LOAD DISTINCT [Sold To], [Sold To] AS [Old Sold To], 1 AS Old_Cust RESIDENT VBRP_R WHERE FISYR >= '$(vPrevYear)' and FISYR < '$(vFisYr)' ; New_Customers: LEFT JOIN (KNA1_NEW) LOAD DISTINCT [Sold To], 1 AS New_Cust RESIDENT VBRP_R WHERE FISYR = '$(vFisYr)' AND NOT EXISTS([Old Sold To], [Sold To]) ; Lost_Customers: LEFT JOIN (KNA1_NEW) LOAD DISTINCT [Sold To], 1 AS Lost_Cust RESIDENT VBRP_R WHERE FISYR <= '$(vLastServiceYear)' AND NOT EXISTS ([Old Sold To], [Sold To]) ;
Sorry Sunny I got pulled to another project. I will be testing this out next week. Sorry for the delay. Thanks for all your help.