Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have a data like below.
Year:
LOAD
* Inline [
Month,Year
1,2014
2,2014
3,2014
4,2014
5,2014
6,2014
7,2014
8,2014
1,2013
2,2013
3,2013
4,2013
5,2013
6,2013
7,2013
8,2013
9,2013
10,2013
11,2013
12,2013
];
If User select Year = 2013
i want an if condition logic that
In above i have 1 to 12 minth for Year = 2013 and for Year = 2014 Month is 1 to 8
I want to create a dimension based on MaxMonth group by year.
Compare that MaxMonth with given Month group by Year.
So, what i want the logic made like below
So, my logic is if Month = 1,'Jan',
Month<=2,'Jan & Feb',
Month>=1 and Month <=3,'Q1',
Month<=4 ,'Apr',
Month>=4 and Month<=5,'Apr & May',
Month>=1 and Month<=6,H1,
Month=7,July,
Month>=7 and Month<=8 ,'July & Aug',
Month> = 7 and Month<=9,'Q3',
Month=10,'Oct',
Month>=10 and Month<=11,'Oct and Nov',
Month>=10 and Month<=12,'Q4',
Month>6 and Month<=12,'H2',
Month>=1 and Month<=12,'YTD Dec' as NewDimension
here the static value m write is MaxMonth of that particular Year.
My Output:
So, for Year =2013
The Value having in NewDimension field is
Q1
Q2
H1
Q3
Q4
H2
YTD Dec
if select Year = 2014
then in NewDimension field having value alike
July & Aug
YTD Aug
Any help will be appreciable.
NoConcatenate
MaxMonth:
Load Year,SubField(NewDim,',',IterNo()) While IterNo() <= SubStringCount(NewDim,',')+1;
Load Year, If(MthSum=12,'Q1,Q2,H1,Q3,Q4,H2,YTD Dec',If(MthSum=11,'Q1,Q2,H1,Q3,Oct and Nov,YTD Nov',If(MthSum=8,'Q1,Q2,H1,July & Aug,YTD Aug'))) As NewDim;
//EDIT
Load Sum(Month) As MthSum, Year Resident Year Group by Year;
Hi anbu1984 ,
Thanks for you reply
I m checking your output but it doesn't link with Month values. like for Year = 2014
if m select
Q1 then it 'll link to Month field values 1 to 3
Q2 then it 'll link to Month field values 4 to 6
H1 then it 'll link to Month field values 1 to 6
July & Aug then it 'll link to Month field values 7 to 8
YTD Aug then it 'll link to Month foeld values 1 to 8.
Hope you understand. and same relevant for Year = 2014
Here above defined logic have mistake for Apr the line is Month=4 ,'Apr'.
Regards
Mohit
Create another table like below and link this table to newly created dimension
Load * Inline [
NewDim,Month
Q1,1
Q1,2
Q1,3
Q2,4
..
Jul & Aug,7
Jul & Aug,8
...
YTD Jan,1
YTD Feb,1
YTD Feb,2
...];
Thanks for reply back.
If my Year table also having the Date field then ?
Not getting your question
Hi
The same logic i applied using master calendar but can't get success.
Please find attachment.
Please help me any suggestion
MaxMonth table is empty. Can you reload and check.
Please find attachment, Here the issue is MthSum is consolidate all value instead of separately as alike your above example. !
