Quarter need to create only one months completed

[sunilkumarqv]

Need small help ,

Examples

Quarter,Month

Q1,Apr

Q1,May

Q1,Jun

Q2,Jul

Q2,Aug

Q2,Sep

Q3,Oct

So, If you see above examples Q1,Q2 Fine because it completes its months, In case of  Q3, no need because months are incomplete

how to achieve this in script    thought Id or any suggestions please

[Anonymous]

try this in a textbox...

=if(month(now(1))<month(QuarterEnd(now(1))), 'Quarter did not end','Quarter ended')

you may be able use this in a where clause in your load statement depending on how your fields are structured if you want to use this in a script or in your calculated dimension if you want to do it in a chart. it is not entirely clear from your post what exactly you want to achieve and how your original table/fields are structured.

[Anonymous]

if you have numeric value for month:

MaxMonthNum:

load max(Monthnum) as LastMonthNum

resident WhereEverMonthRemains

vLastMonthNum = peek('MaxMonthNum',0,'LastmonthNum)

if (vLastQuarterNum=div(vLastMonthNum,3);

Calendar:

load

Month,

if (Monthnum<=vLastQuarterNum, 'Q' & ceil(MonthNum,3),null()) as Quarter

resident whatssover

Result should be

Q1, Jan

Q1, Feb

Q1, Mar

Q2, Apr

Q2, May

Q2, Jun

--, Jul

when last Month is july

The quarter will only be feilled when it is completed

-> your calendar seems to be a Business calendar, you have to adjust this aspect

[MarcoWedel]

Hi,

one solution could be:

tabCalendar:
LOAD Date,
    Week(Date) as Week,
    MonthName(Date) as Month,
    Year(Date) as Year;   
LOAD Date(Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1) as Date
AutoGenerate 1
While Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1 <= Today();

tabTemp:
LOAD Distinct
  QuarterName(Date) as QuarterName,
  Month
Resident tabCalendar;

Left Join (tabTemp)
LOAD QuarterName,
    Count(DISTINCT Month) as MonthCount
Resident tabTemp
Group By QuarterName;

Left Join (tabCalendar)
LOAD Month,
    QuarterName,
    Dual('Q'&(Month(QuarterName)+2)/3, (Month(QuarterName)+2)/3) as Quarter
Resident tabTemp
Where MonthCount = 3;

DROP Table tabTemp;

hope this helps

regards

Marco

[sunilkumarqv]

This not working asap

[sunilkumarqv]

Hi MAcro,

Any alternative like create  to YearQuater ID  that to autogenerate when month of quarter complete if quarter not completed then Id is not required .

[MarcoWedel]

sorry, I didn't get this.

regards

Marco