topic Re: Help with a formula please in QlikView

Help with a formula please

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

Hi,

I am new to QlikView (2 days) and am trying to create an expression to give me a number of columns of sales document totals (sum of SUBTOTAL_2) by year, based on a document date (DOCDATE_2)

I have tried the one below but it is not working.

Col 1. Sum({<=(year(DOCDATE_2))={2018}>}SUBTOTAL_2)

Col 2. Sum({<=(year(DOCDATE_2))={2017}>}SUBTOTAL_2)

etc.

Can anyone correct my errors please or suggest an alternative.

Thanks.

Re: Help with a formula please

sunny_talwar — Wed, 27 Jun 2018 15:39:56 GMT

Create the year field in the script while loading the data like this

LOAD DOCDATE_2,

Year(DOCDATE_2) as YEAR

FROM ...;

And then try these

Sum({<YEAR = {'2018'}>} SUBTOTAL_2)

Sum({<YEAR = {'2017'}>} SUBTOTAL_2)

Re: Help with a formula please

vishsaggi — Wed, 27 Jun 2018 21:24:05 GMT

Can he also write like this sunny?

= Sum({< SUBTOTAL_2 = {"$(= year(DOCDATE_2) = '2018')" } >}SUBTOTAL_2)

Re: Help with a formula please

Anil_Babu_Samineni — Thu, 28 Jun 2018 02:47:49 GMT

No, This won't work. But mostly below solutions will work when you don't want to disturb script. But, I won't think to do in UI instead my suggestion always script in this way. This can help mostly "Performance Tuning" concepts.

= Sum({<SUBTOTAL_2={"=Year(DOCDATE_2)=2018" } >}SUBTOTAL_2)

= Sum({<SUBTOTAL_2={"=Year(DOCDATE_2)=2017" } >}SUBTOTAL_2)

Alternatively, You can also try this way

=Sum({<DOCDATE_2 = {"=Match(Year(DOCDATE_2),Year(Max(TOTAL DOCDATE_2)))"}>} SUBTOTAL_2)

=Sum({<DOCDATE_2 = {"=Match(Year(DOCDATE_2),Year(Max(TOTAL DOCDATE_2))-1)"}>} SUBTOTAL_2)

Re: Help with a formula please

vishsaggi — Thu, 28 Jun 2018 03:12:16 GMT

Oops you are right i do not need $ sign in this.