Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! I am so stumped on this and I cannot figure out why!
Here is my script:
Claims:
LOAD [Cust. Service/Distribution],
[Client Name],
Facility,
[Ship Date],
date(date#([Ship Date],'MM-DD-YYYY'),'MM/DD/YYYY') AS [SD],
Num(Date([Claim Date])) AS [ClaimDate],
date(date#([Claim Date],'MM-DD-YYYY'),'MM/DD/YYYY') AS [CD],
Year(date(date#([Claim Date],'MM-DD-YYYY'),'YYYY')) AS Year1,
Month(date(date#([Claim Date],'MM-DD-YYYY'),'MMM')) AS Month1,
Day(date(date#([Claim Date],'MM-DD-YYYY'),'DD')) AS Day1,
[Claim Date],
[Invoice Number],
[Order Number],
[Type of Claim],
[Claim Count],
[List item #s and QTY to be replaced/sent],
[Items received in error (List item #'s and QTY)],
[WH Employee Name],
[CSR Comments],
[Error],
date(date#([Finish Date],'MM-DD-YYYY'),'MM/DD/YYYY') as [Finish Date]
FROM
[D:\Reports- Production\Base Extracts\Claims.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Here is my Master Calendar:
LET TodaysDate = num(Today());
LET CurrentYear = Year(Today());
LET PreviousYear = Year(Today()-365);
DateLink:
LOAD
[Invoice Number],
[CD] as Date,
'Claim' as DateType
Resident Claims;
Minmaxdates:
LOAD min([Date]) as mindate,
max([Date]) as maxdate
Resident [DateLink];
LET vMinDate = Num(Peek('mindate',0,'minmaxdates'));
LET vMaxDate = Num(Peek('maxdate',0,'minmaxdates'));
DROP Table Minmaxdates;
DateRanges:
LOAD $(vMinDate) + IterNo()-1 as dateNum,
Date($(vMinDate) + IterNo()-1) as Date
AutoGenerate 1
While $(vMinDate) + IterNo()-1 <= $(vMaxDate);
MasterCalendar:
LOAD Date(Date,'DD-MM-YYYY') as [Date],
Day(Date) as Day,
Week(Date) as Week,
Month(Date) as Month,
'Q' & Ceil(Month(Date)/3) as Quarter,
Year(Date) as Year
Resident DateRanges Order by Date asc;
DROP Tables DateRanges;
Here is my expression:
Current Year: =Sum({$<[Year]={$(=Year(today()))}>}[Claim Count])
Previous YTD:
SUM({$<Year=, Quarter=, Month=, Week=, Date = {">=$(=YearStart(Max(Date),-1))<=$(=AddYears(Max(Date),-1))"}>}[Claim Count])
My numbers do not match what a true LYTD would be. Also when I try to use any date selections my bar graph goes blank even when there were claims on those dates.
Any Help is appreciated!
hi
can you share the your ERD to see the connections between the the tables
The one thing that has me puzzled here is the duplicate WeightCheck tables, the one that is connected to the data model and the one that is a data island. That could potentially be part of the issue here if that is in fact there, was not sure if it was a glitch in the screenshot or what...
Regards,
Brett