Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
sunny Sunny,
The sort order should be in the table should be Current,1-30,31-60,61-90,91-120,121-150,151-179,179-180
Table1
I have two dimensions, Vintage and DLQT_STAT: And have an expression as Sum(Sales).
Now I have to create a new pivot or straight table.
Unable to explain the logic, I have created a excel with formula.
Table2 has same dimensions just need to change the expression. Any idea how I can achieve it?
Thanks,
Pavan
Would this do the job?
Sum(Sales)/Above(Before(Sum(Sales)))
The numbers from the Excel are not matching with number from the sample. But see if this is right
Hi Sunny, the expression you sent was correct and worked fine but there is one issue. If you make any selection in the chart, the chart gets screwed right. So to avoid that I tried to tweak my table.
Vintage:
Load DLQ_STATUS,
YR_MTH_KEY,
CRRNT_PRCPL_BAL_AMT
, '1' as Vintage_flag
from
\\qa01\QVSERVER\QVSOURCEDOCS\QVW_Extract\T_AR_ACCT_MTH_END_Vintage.qvd(qvd);
Concatenate
load
DLQ_STATUS,
YR_MTH_KEY,
YR_MTH_KEY-1 as YR_MTH_KEY1,
CRRNT_PRCPL_BAL_AMT,
'2' as Vintage_flag
Resident Vintage;
Now my dimension would be YR_MTH_KEY and I'll have to write my expressions as
sum({<DLQ_STATUS = {1},Vintage_flag = {2}>}CRRNT_PRCPL_BAL_AMT)/sum({<YR_MTH_KEY1 = {'YR_MTH_KEY-1'},DLQ_STATUS = {0},Vintage_flag = {2}>}CRRNT_PRCPL_BAL_AMT)
But I'm missing something in my expression? Any idea what? Hope my question makes sense.
I won't know the issue until I see it, may be try this:
Sum(Sales)/Above(Before(Sum({<DLQT_STAT>}Sales)))
This looks good?
Expression:
Sum(Sales)/Above(Before(Sum({<DLQT_STAT, Vintage>}Sales)))
Sort expression for your second dimension
Match(Only({<DLQT_STAT, Vintage>}DLQT_STAT), '0-0', '1-30', '31-60', '61-90', '91-120', '121-150', '151-179', '179-180')