Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikepitcher
Contributor
Contributor

Help with a formula please

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.

4 Replies
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
Champion III
Champion III

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vishsaggi
Champion III
Champion III

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