Count employes for every week

[mauvasco62]

Hi all,
I need your help in order to create a data load script.
I have a situation like this related to a list of employment contracts.

• ContractID
• Employ ID
• Contract start
• Contract end  --> where in some cases it could be empty because the worker is active

I would like to create a report related to previous 12 weeks and for every week i would like to count the number of workers.

The final result could be like this:

Week             number of employes

44                    151

45                     150

46                     147

47                     122

I have an idea : to use iterno() but i'm not sure

Could you help me

Thanks

Mauro

[Kushal_Chawda · Accepted solution]

You can generate the data

load ContractID,

           EmployeeID,

            ContractStart + iterno()-1 as Date,

FROM table

while ContractStart + iterno()-1<= ContractEnd;

Now using Date filed you can generate calendar including week. Then you can create a report with dimension week and measure count(distinct employees)