topic Re: Quarter need to create only one months completed in QlikView

Quarter need to create only one months completed

sunilkumarqv — Fri, 22 Aug 2014 13:42:03 GMT

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

Re: Quarter need to create only one months completed

Anonymous — Fri, 22 Aug 2014 14:13:54 GMT

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.

Re: Quarter need to create only one months completed

Anonymous — Fri, 22 Aug 2014 14:17:14 GMT

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

Re: Quarter need to create only one months completed

MarcoWedel — Fri, 22 Aug 2014 22:00:49 GMT

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

Re: Quarter need to create only one months completed

sunilkumarqv — Sat, 23 Aug 2014 06:25:56 GMT

This not working asap

Re: Quarter need to create only one months completed

sunilkumarqv — Sat, 23 Aug 2014 08:53:04 GMT

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 .

Re: Quarter need to create only one months completed

MarcoWedel — Sat, 23 Aug 2014 09:30:27 GMT

sorry, I didn't get this.

regards

Marco