13 CYCLE MONTHS with in A YEAR

[kakani87]

Dear All,

I'm having a scenario to display 13 cycle months based on the dates given like 1/4/2017 to 31/4/2017

Now i'm clear till this but my question is how to display the missing dates for each cycle with given nickname

available data

Date             |  Cycle Month                  |  Nickname

01-04-2017     CYCLE MONTH 01          UNIFY

30-04-2017     CYCLE MONTH 02          POLAR

28-05-2017     CYCLE MONTH 03          ACTIVE

25-06-2017     CYCLE MONTH 04          DEFINE

23-07-2017     CYCLE MONTH 05          EMPOWER

20-08-2017     CYCLE MONTH 06          ORGANISE

17-09-2017     CYCLE MONTH 07          CHANNEL

15-10-2017     CYCLE MONTH 08          HARMONY

12-11-2017      CYCLE MONTH 09         PULSE

10-12-2017      CYCLE MONTH 10         PERFECT

07-01-2018      CYCLE MONTH 11          DISSOLVE

04-02-2018      CYCLE MONTH 12          DEDICATE

04-03-2018      CYCLE MONTH 13          COSMIC

my requirement is something like this

Date             |  Cycle Month                  |  Nickname

01-04-2017     CYCLE MONTH 01          UNIFY

02-04-2017     CYCLE MONTH 01          UNIFY

03-04-2017     CYCLE MONTH 01          UNIFY

.

.

.

29-04-2017     CYCLE MONTH 01          UNIFY

30-04-2017     CYCLE MONTH 02          POLAR

01-05-2017     CYCLE MONTH 02          POLAR

02-05-2017     CYCLE MONTH 02          POLAR

.

.

.

27-05-2017     CYCLE MONTH 02          POLAR

28-05-2017     CYCLE MONTH 03          ACTIVE

29-05-2107     CYCLE MONTH 03          ACTIVE

.

.

.

24-06-2017     CYCLE MONTH 03          ACTIVE

25-06-2017     CYCLE MONTH 04          DEFINE

.

.

.

.

.

i want to display the records in this way because this dates should be associated with normal 12 months calendar and then i need to build a report for cycle month

Kindly find the attached sample Qvw and do the needful.

Regards,

Kakani.

[sunny_talwar · Accepted solution]

Try this

Cycle_Month:

LOAD c_code,

    cycle_name,

    Date,

    nick_name

FROM

cycle_month.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTable:

LOAD *,

Alt(Peek('Date') - 1, Today()) as End_Date

Resident Cycle_Month

Order By Date desc;

FinalTable:

NoConcatenate

LOAD c_code,

    cycle_name,

    Date(Date + IterNo() - 1) as Date,

    nick_name

Resident NewTable

While Date + IterNo() - 1 <= End_Date;

DROP Table Cycle_Month, NewTable;

[kakani87]

Thank you very much Sunny for the response.

[kakani87]

Hi Sunny,

Suppose to be 13 cycle months as per my requirement but here it is displaying only 12 months could you check this once

[sunny_talwar]

But based on the output you asked for, I am not sure where does 13 month comes from? Would you be able to provide the expected output date range more clearly?