topic Simplify expression of sum in QlikView

Simplify expression of sum

Anonymous — Tue, 04 Aug 2015 09:56:53 GMT

How can I simplify the following expression , vFromDate and vToDate are date types .

sum({$<Date ={">=$(vFromDate) <=$(=if((right($(vToDate),2) ='31' and right(left($(vToDate),6),2) ='12' ),left(AddYears(vToDate,1),4)&'01'&'01',if(right($(vToDate),2) ='31', left(vToDate,4)&right(left(AddMonths(vToDate,1),7),2)&'01', left($(vToDate),6)&(right($(vToDate),2) + 1))))"}>}[Prix de vente TTC])

Re: Simplify expression of sum

MayilVahanan — Tue, 04 Aug 2015 10:00:42 GMT

Put below part into variable and use that variable in set analysis

vToDateSimply

=if((right($(vToDate),2) ='31' and right(left($(vToDate),6),2) ='12' ),left(AddYears(vToDate,1),4)&'01'&'01',if(right($(vToDate),2) ='31', left(vToDate,4)&right(left(AddMonths(vToDate,1),7),2)&'01', left($(vToDate),6)&(right($(vToDate),2) + 1)))

sum({$<Date ={">=$(vFromDate) <=$(vToDateSimply)"}>}[Prix de vente TTC])

Re: Simplify expression of sum

rubenmarin — Tue, 04 Aug 2015 10:06:04 GMT

Hi, maybe this:

sum({$<Date ={">=$(vFromDate) <=$(=Date(Date#('$(vToDate)', 'YYYYMMDD')+1, 'YYYYMMDD')"}>}[Prix de vente TTC])

Re: Simplify expression of sum

sasiparupudi1 — Tue, 04 Aug 2015 10:16:02 GMT

What is the format of the date?

if the dates are in QV standard format then you could use QV functions like

day,Month and Year to get to the day,month and year respectively.

day('2015-02-02')

num(month('2015-02-02'))

if the dates are in custom format like 20150531

then you have to use date#

day(date#('20150531','YYYYMMDD'))

HTH

Sasi