Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue with exp

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

1 Solution

Accepted Solutions
sunny_talwar

Would this do the job?

Sum(Sales)/Above(Before(Sum(Sales)))

View solution in original post

5 Replies
sunny_talwar

Would this do the job?

Sum(Sales)/Above(Before(Sum(Sales)))

sunny_talwar

The numbers from the Excel are not matching with number from the sample. But see if this is right

Capture.PNG

Not applicable
Author

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.

sunny_talwar

I won't know the issue until I see it, may be try this:

Sum(Sales)/Above(Before(Sum({<DLQT_STAT>}Sales)))

sunny_talwar

This looks good?

Capture.PNG

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')