How to display previous month data for current month if no data for current month

[#1]

Hi All,

I have chart for last 6th month count(Workers)

issue:i dont have data for July Month

i want to show June data  for  July also.

if there is no data for Any Month need to take previus month data .

How to achieve this

please find the application sample

[#2]

Hi Raj,

Try appending this to your script.

LOAD *,Date(AddMonths(MonthName, IterNo()), 'MMM YYYY') as MonthName

While AddMonths(MonthName, IterNo())<NextMonth; 

LOAD *, 

    Previous(MonthName) as NextMonth 

Resident HeadCount

Order By [Worker ID],MonthName Desc;

Hope it helps.

Regards,

Pratik

[#3]

iam not able to get desired solution,

can u post sample

[#4]

Hi Raj,

Try This:

HeadCount:

LOAD

     [Job Posting Title],

     [Worker: Worker Supervisor],

     [Worker: Worker Supervisor] as [Hiring Manager],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     Month([Start Date]) as Month,

     month([End Date]) as DOR,

     Year(([Start Date])) as Year,

     MonthName([Start Date]) as MonthName,

     MonthName([End Date]) as MonthName1,

     today() as CM,

     AddMonths(today(),-6) as R6M,

     'Q' & ceil(Month([End Date])/3) as Quarter,

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

FROM

(biff, embedded labels, table is Headcount$, filters(

Remove(Row, RowCnd(CellValue, 4, StrCnd(null)))

));

///// New Script to be added after your table..

LOAD [Job Posting Title],

     [Worker: Worker Supervisor],

     [Worker: Worker Supervisor] as [Hiring Manager],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     Month([Start Date]) as Month,

     month([End Date]) as DOR,

     Year(([Start Date])) as Year,

     Date(AddMonths(MonthName, IterNo()), 'MMM YYYY') as MonthName,

     MonthName([End Date]) as MonthName1,

     today() as CM,

     AddMonths(today(),-6) as R6M,

     'Q' & ceil(Month([End Date])/3) as Quarter,

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

While AddMonths(MonthName, IterNo())<=NextMonth;

LOAD *,

    if(Month<MonthName(Today()) and Month=MonthName(AddMonths(Today(),-1)),MonthName(Today())) as NextMonth 

Resident HeadCount

Order By [Worker ID],MonthName Desc;

If this doesnt give the desired result then post ur excel with dummy data if possible..

Regards,

Pratik

[#5]

Hi Pratik ,

Thanks for your Time.

please find the data files

Thanks in advance

[#6]

Hi Raj,

Try This:

HeadCount:

LOAD

     [Job Posting Title],

     [Worker: Worker Supervisor],

     [Worker: Worker Supervisor] as [Hiring Manager],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     Month([Start Date]) as Month,

     month([End Date]) as DOR,

     Year(([Start Date])) as Year,

     MonthName([Start Date]) as MonthName,

     MonthName([End Date]) as MonthName1,

     today() as CM,

     AddMonths(today(),-6) as R6M,

     'Q' & ceil(Month([End Date])/3) as Quarter,

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

FROM

(biff, embedded labels, table is Headcount$, filters(

Remove(Row, RowCnd(CellValue, 4, StrCnd(null)))

));

///// New Script to be added after your table..

LOAD [Job Posting Title],

     [Worker: Worker Supervisor],

     [Worker: Worker Supervisor] as [Hiring Manager],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     Month([Start Date]) as Month,

     month([End Date]) as DOR,

     Year(([Start Date])) as Year,

     Date(AddMonths(MonthName, IterNo()), 'MMM YYYY') as MonthName,

     MonthName([End Date]) as MonthName1,

     today() as CM,

     AddMonths(today(),-6) as R6M,

     'Q' & ceil(Month([End Date])/3) as Quarter,

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

While AddMonths(MonthName, IterNo())<=NextMonth;

LOAD *,

    if(MonthName <MonthName(Today()) and MonthName =MonthName(AddMonths(Today(),-1)),MonthName(Today())) as NextMonth

Resident HeadCount

Order By [Worker ID],MonthName Desc;

[#7]

I tried it worked.

Let me know if that worked for you.

Regards,

Pratik

[#8]

can u post the application

thanks

my Script:

HeadCount:

LOAD CM,

     [Cumulative Committed Spend],

     "Current Bill Rate [ST/Hr]",

     DOR,

     [End Date],

     [Hiring Manager],

     [Job Posting Title],

     Month,

    MonthName,

      //Date(AddMonths(MonthName, IterNo()), 'MMM YYYY') as MonthName,

     MonthName1,

     [Physical Location of Worker],

     Quarter,

     R6M,

     [Start Date],

     Status,

     [Total Spend],

     Worker,

     [Worker Closed Date],

     [Worker ID],

     [Worker: Worker Supervisor],

     Year

FROM

[31-07-2015\head.xlsx]

(ooxml, embedded labels, table is Sheet1);

Resident1:

LOAD *,

    if(MonthName <MonthName(Today()) and MonthName =MonthName(AddMonths(Today(),-1)),MonthName(Today())) as NextMonth

Resident HeadCount

Order By [Worker ID],MonthName Desc;

drop table HeadCount;

Actual:

LOAD [Job Posting Title],

     [Worker: Worker Supervisor],

     [Worker: Worker Supervisor] as [Hiring Manager],

     Worker,

     [Worker ID],

     Status,

     [Start Date],

     [End Date],

     Month([Start Date]) as Month,

     month([End Date]) as DOR,

     Year(([Start Date])) as Year,

     Date(AddMonths(MonthName, IterNo())) as MonthName,

     MonthName([End Date]) as MonthName1,

     today() as CM,

     AddMonths(today(),-6) as R6M,

     'Q' & ceil(Month([End Date])/3) as Quarter,

     "Current Bill Rate [ST/Hr]",

     [Physical Location of Worker],

     [Worker Closed Date],

     [Cumulative Committed Spend],

     [Total Spend]

     resident Resident1 While AddMonths(MonthName, IterNo())<=NextMonth;

     drop table Resident1;

[#9]

Hi Raj,

Sorry I can post app. I have tried doing it again using your script. I am getting the values for july. Since the month of august has started your excel data will comprise of July months data which you will be wanting to show for the month of august as well right. So I added two records in the head.xlsx sheet below are the records:

CM

Cumulative Committed Spend

Current Bill Rate [ST/Hr]

DOR

End Date

Hiring Manager

Job Posting Title

Month

MonthName

MonthName1

Physical Location of Worker

Quarter

R6M

Start Date

Status

Total Spend

Worker

Worker Closed Date

Worker ID

Worker: Worker Supervisor

Year

8/30/2015

10966.8

21.09

Aug

8/28/2015

Ebwkgaovrc, Ipatpp

IT Intern - non CA

Jul

Jul 2015

Aug 2015

Berkeley Heights, NJ - 300

Q3

1/30/2015

7/1/2015

Open

0

Siegert, Rebecca

CELGWK00001663

Gibzrepfkk, Nrphlz

2015

8/30/2015

11625.84

21.69

Sep

9/1/2015

Cngbshcadq, Depeu

IT Intern - non CA

Jul

Jul 2015

Sep 2015

Berkeley Heights, NJ - 300

Q3

1/30/2015

7/1/2015

Open

0

Patel, Bijal

CELGWK00001661

Ehqomjfgcq, Dzkvp

2015

If now you will get these two records which I added for july in the month of august(Current Month) as well.

If you see in the HeadCount tables script I have commented the MonthName field and derived a MonthName field from start date, which offcourse you are doing in excel, I purposely did that because if I am using the MonthName field I am getting wrong counts maybe because of Data issue(Syntax in MonthName field). Not sure. So I used the derived field. You can check for yourself by uncommenting the MonthName and Commenting the Derived MonthName field for your reference. When doing this also dont forget to comment and uncomment the MonthName similarly in the Below script of Preceding Load.

HeadCount:

LOAD CM,

    [Cumulative Committed Spend],

    "Current Bill Rate [ST/Hr]",

    DOR,

    [End Date],

    [Hiring Manager],

    [Job Posting Title],

    Month,

    //MonthName,

      MonthName([Start Date]) as MonthName,

    MonthName1,

    [Physical Location of Worker],

    Quarter,

    R6M,

    [Start Date],

    Status,

    [Total Spend],

    Worker,

    [Worker Closed Date],

    [Worker ID],

    [Worker: Worker Supervisor],

    Year

FROM

\\MSAD\ROOT\EU\LN\USERS\pratic\Desktop\head.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD CM,

    [Cumulative Committed Spend],

    "Current Bill Rate [ST/Hr]",

    DOR,

    [End Date],

    [Hiring Manager],

    [Job Posting Title],

    Month,

    //AddMonths(MonthName, IterNo()) as MonthName,

    Date(AddMonths(MonthName, IterNo()), 'MMM YYYY') as MonthName,

    MonthName1,

    [Physical Location of Worker],

    Quarter,

    R6M,

    [Start Date],

    Status,

    [Total Spend],

    Worker,

    [Worker Closed Date],

    [Worker ID],

    [Worker: Worker Supervisor],

    Year

While AddMonths(MonthName, IterNo())=NextMonth;

LOAD *,

    if(MonthName<MonthName(Today()) and MonthName=MonthName(AddMonths(Today(),-1)),MonthName(Today())) as NextMonth

Resident HeadCount ;

Regards,

Pratik