Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
can anyone tell me how get this output?
This is my data.
Sample:
LOAD * INLINE [
Company, Sub, Id, Exp 1, Exp 2
ABC, A1, 1, 5, 8
ABC, A1, 2, 10, 13
ABC, A1, 3, 13, 16
ABC, A1, 4, 15, 18
ABC, A1, 5, 17, 20
ABC, A2, 6, 19, 22
ABC, A2, 7, 20, 23
ABC, A2, 8, 25, 28
ABC, A2, 9, 30, 33
];
Output:
Company | Sub | Id | Exp 1 | Exp 2 | Logic |
ABC | A1 | 1 | 5 | 8 | |
ABC | 2 | 10 | 13 | ||
ABC | 3 | 13 | 16 | ||
ABC | 4 | 15 | 18 | ||
ABC | 5 | 17 | 20 | ||
ABC | A2 | 6 | 19 | 22 | 19 |
ABC | 7 | 20 | 23 | 20+(23-22) | |
ABC | 8 | 25 | 28 | 25+(28-23) | |
ABC | 9 | 30 | 33 | 30+(33-28) |
will it check everytime maximum sub area ?
Why bcoz i have next sub area i need to caluculate from the max sub.Will it work that time ?
Yup, if you have a sub area named A3 coming then the logic column will show values for A3 alone and not A1, A2.
I checked by giving some random values for A3. It worked.
Regards
Harish
Hi Anil babu
Please find the below logic
Pivot Table:
Dimensions –
Company, Sub, ID
Measures -
Exp1 =Sum(Exp1)
Exp2=Sum(Exp1)
Logic =If(Right(Sub,1)=Max(Total<Company>Right(Sub,1)),If(RowNo()=1,Exp1,(Exp1+Exp2)-(RangeSum(Above(Exp2,1)))))
Regards
Harish
I assume that max sub is calculated based on its ID like A1, A2, A3.
Please let me know if max sub is calculated based on dif logic.
Regards
Harish
Thankq harish,
But i f i remove Sub and Company from dimentions , values are coming wrong
My requirement is to show in combo chart , i have id as dim rest exp are same.
Can you show some sample image of what you are expecting your output to be.
Regards
Harish
Values are correct if have Sub in dimention,but i want same values if i take ID dim also.
Hi,
Always try to explain the question clearly. Your last question is deviating from your initial question. Check the attachment.
hi Nagaraj,
One issue coming again ,
If i take dim as Date instead of date it is showing number.
Can u tell me solution for it ?
Hi Subbareddy,
You can use calculated dimension like below.
=Date(Datefield,'YourFormat')
Ex: =Date(Cal.Date,'DDMMYYYY')
If it's not working, I would suggest to post a sample file.