Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

YTD and LYTD Bar Graph

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! 

 

Labels (5)
3 Replies
Partner
Partner

Re: YTD and LYTD Bar Graph

hi 

can you share the your ERD to see the connections between the the tables 

Partner
Partner

Re: YTD and LYTD Bar Graph

 
Support
Support

Re: YTD and LYTD Bar Graph

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

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.