topic Re: Expression Question in QlikView

Expression Question

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the attached Straight table.

Dimensions = trade_dt and period_cd_my

Expression = Sum(pos_lots)

Is there a way to show the sum of the position lots where the trade_dt is less than or equal to the dimension trade_dt?

ie Sum({<trade_dt={'<=trade_dt'}>}pos_lots)

Any help will be much appreicated.

Regards,

Daniel

Re: Expression Question

richnorris — Fri, 20 May 2016 14:04:09 GMT

What do you mean by "where the trade_dt is less than or equal to the dimension trade_dt?" do you mean, where the dimension trade_dt is less than or equal to the summation of all trade_dt's for one period_cd_my?

Re: Expression Question

Kushal_Chawda — Fri, 20 May 2016 14:14:11 GMT

did not understand this line "where the trade_dt is less than or equal to the dimension trade_dt"

Re: Expression Question

danielnevitt — Fri, 20 May 2016 14:14:54 GMT

Hi Richard,

Thank you for the reply.

I believe I do mean where the dimension trade_dt is less than or equal to the summation of all trade_dt's for one period_cd_my.

Regards,

Daniel

Re: Expression Question

PabloOrtiz — Fri, 20 May 2016 14:46:40 GMT

Hi, can you upload an example that the result of the table you are find?

Re: Expression Question

danielnevitt — Fri, 20 May 2016 15:01:47 GMT

Hi Pablo,

Attached is some test data.

The column in red is what I am trying to produce. This is the sum total of all the days data less than the trade_dt in that row.

I hope this makes sense.

Regards,

Daniel

Re: Expression Question

Kushal_Chawda — Fri, 20 May 2016 15:14:24 GMT

see this

Data:
LOAD ProductName,
trade_dt,
period_cd_my,
[Sum(pos_lots)] as pos_lots
FROM

(ooxml, embedded labels, table is Sheet1);

New:
NoConcatenate
LOAD *,
if(period_cd_my=Previous(period_cd_my),RangeSum(peek('Output'),pos_lots),pos_lots) as Output
Resident Data
Order by ProductName,period_cd_my,trade_dt;

DROP Table Data;

Re: Expression Question

PabloOrtiz — Fri, 20 May 2016 15:36:47 GMT

!Hi, i atached an .qvw

Re: Expression Question

danielnevitt — Fri, 20 May 2016 15:37:23 GMT

Thanks for your response Kushal.

Is it possible to write this as an expression within the straight table?

Regards,

Daniel

Re: Expression Question

Kushal_Chawda — Fri, 20 May 2016 15:44:32 GMT

i think pablo.ortiz already answered

Re: Expression Question

Kushal_Chawda — Fri, 20 May 2016 16:15:14 GMT

I would suggest to go for script solution because on selection front end solution will not work properly and would be very complex to get it work. Best practice always says that, you should handle most of the condition in script if it is possible

Re: Expression Question

PabloOrtiz — Fri, 20 May 2016 16:19:18 GMT

I agree whith you Kushal.

Re: Expression Question

johnw — Fri, 20 May 2016 22:45:27 GMT

This sounds like a good place to use an AsOf table. We can build a table that links each AsOfDate to that and every previous trade_dt. It lets you avoid doing accumulations in the script while still solving the problem with script. There are a lot of ways to generate such a table. This is one.

AsOf:
LOAD date(fieldvalue('trade_dt',recno())) as trade_dt
AUTOGENERATE fieldvaluecount('trade_dt')
;
LEFT JOIN (AsOf)
LOAD trade_dt as AsOfDate
RESIDENT AsOf
;
INNER JOIN (AsOf)
LOAD *
RESIDENT AsOf
WHERE trade_dt <= AsOfDate
;