Use of Row() to get WeekN for distinct date - script - not working

[ashmitp869]

Hi there,

How to use RowNo() in script to get the WeekN automatically in Qlik App 

I have a excel file like below with other fields which is manual entry. My req is to make WeekN to be automatic i,e without the data entry .

I am using the below script - 

WeeklyCovidNo1:
LOAD
MakeDate(Year([Today's date]),Month([Today's date]), Day([Today's date])) as _Date,
"COVID +ve patients in ICU",
"COVID +ve patients in ICU ,
"COVID +ve patients in wards ",
"COVID +ve patients in wards"
FROM [lib:/testing.xlsx]
(ooxml, embedded labels, table is Form1);

WeeklyCovidNo:

noconcatenate
Load
Distinct _Date
,
RowNo() as WeekN
Resident WeeklyCovidNo1 ;

Drop table WeeklyCovidNo1;

But I am getting like below- each entry with no.

Please kindly assist me with the script - So that WeekN is calculated automatically.

[dmac1971]

This should work?  (Week(Today())-Week(Date))-1 As WeekN

[ashmitp869]

Hi there,

this is woking but it won't work for new year.

It restarted to be week 1. Then 1-52=-51.

Can you help me how to get distinct date and then count the rowno().

[dmac1971]

RowNo() probably isnt the right approach here.  You should be easily able to adapt the Week approach?

[ashmitp869]

then how to solve new year -

It restarted to be week 1. Then 1-52=-51.

Please assist.