topic Re: Issue with exp in QlikView

Issue with exp

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Issue with exp

sunny_talwar — Wed, 28 Sep 2016 18:27:17 GMT

Would this do the job?

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

Re: Issue with exp

sunny_talwar — Wed, 28 Sep 2016 18:28:59 GMT

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

Re: Issue with exp

Fri, 30 Sep 2016 18:06:06 GMT

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.

Re: Issue with exp

sunny_talwar — Fri, 30 Sep 2016 18:29:46 GMT

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

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

Re: Issue with exp

sunny_talwar — Fri, 30 Sep 2016 18:35:34 GMT

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