Help with a formula please

[mikepitcher]

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.

[sunny_talwar]

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)

[vishsaggi]

Can he also write like this sunny?

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

[Anil_Babu_Samineni]

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)

[vishsaggi]

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