Calculation of amount of weeks?

[Anonymous]

Hello,

how can I count amount of weeks from now to date I get from DATE field. DATE field contains values like this: 20170922, 20170513,.... I need to calculate how many weeks it is always from present day minus dates in that field DATE. So present day - 20170922=amount of weeks. Thx in advance!

[PrashantSangle]

use week()

try like

week(today())- week(week(date#(YourdateField,'YYYYMMDD')))

Regards,

[Anonymous]

Thx for your reply but unfortunately it returns wrong values.

For example for value 20170923 it´s 41weeks.

For value 20170818 it´s 42 weeks.

For value 20161222 it´s 39 weeks.

[its_anandrjs]

I assume you have correct and pure date filed why not try this way.

Week(DateField) as WeekField

And on UI part count values this way

=Count( Distinct WeekField)

[PrashantSangle]

can you tell us logic behind it???

How did you get this.

For Value 20170923 it´s 41weeks.

For value 20170818 it´s 42 weeks.

For value 20161222 it´s 39 weeks

Regards,

[Anonymous]

I´ve used your formula: week(today())- week(week(date#(DATE,'YYYYMMDD')))

In one column I have dates from DATE field and in the second one are results like you see above.

[Anonymous]

Thx for your help but in table box object I cannot use expression or calculated dimension.

[MarcoWedel]

Hi,

maybe one solution could be:

table1:
LOAD *,
    (WeekStart(Today())-WeekStart(DATE))/7 as Weeks;
LOAD Date#(DATE,'YYYYMMDD') as DATE   
Inline [
    DATE
    20161222
    20170513
    20170818
    20170922
    20170923
];

hope this helps

regards

Marco