reading records in tabel based on week & year only

[JACKNIEKERK]

tabel has date values in record from

170101 untill 201231, named InkInkdate

Full tabel is loaded as qvd file

i have choicelist with clicks
for 2017 2018 2019 2020 years
returns clicked value in YearField

Plus a Calander with weeknumbers
returns clicked value in PurchaseweekField

During load say this:

week(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-'& left(InkInkdate,2)) as inkoopweek,
month(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-'& left(InkInkdate,2)) as inkoopmaand,
DayName(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-'& left(InkInkdate,2)) as inkoopdag,
Date#(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-20'& left(InkInkdate,2)) as inkoopDatum,
WeekDay(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-20'& left(InkInkdate,2))as inkoopweekdag,
Year(Right(InkInkdate,2)) as jaar,

In graphic tabel i read now by inkoopweek, and YearField
still get full tabel in graphic in a week all years

need logic to show only week in chosen year records

thanks for advice

[QFabian]

Hi @JACKNIEKERK , try using just week(), because it gives you the number of the week, and you can create a line chart with Year and Week as dimensions.

[JACKNIEKERK]

hello,  thats the problem now, i get all dates with that week for all years

i need some logic to get only weeks from a choosen year

[JACKNIEKERK · Accepted solution]

Ok, solved, trial and error

I created at load this:  (found on this forum)

[ControlCalendar]:
LOAD
*,
MakeWeekDate(YearField,WeekField) as YearWeekDate; //Date based on Year and Week field values from the inline table below..
//Table with only Year and Week number values starting at 2017 , created untill 2022 for now
LOAD * INLINE [YearField, WeekField
2017, 1
2017, 2
2017, 3
2017, 4
2017, 5
2017, 6
2017, 7

Then in load Tabel     (field (inkinkdate)  in tabel is like   yymmdd)

week(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-'& left(InkInkdate,2)) as WeekField,
Date#(Right(InkInkdate,2)&'-'& mid(InkInkdate,3,2)&'-20'& left(InkInkdate,2)) as YearWeekDate,

Then in Graphic these 2 values matter  for showing records  [YearField, WeekField} 

Thanks for advice anyway

Regards Jack