Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.

Point in Time Help

Not being cooperative   it should return the prior years sales with the current point in time keys. in this case: ( January thru July of last year. )

Any ideas?

sum({1<AcctNum = {'Sales'}, Month = {'>=$(=vFirstMonthofYear), <=$(=vCurrentMonth )'}, Year = ${=PriorYear}>} SalesAmount)

Load Script:

  SET vFirstMonthofYear = 'Jan'

  LET CurrentMonth = Month(Today())

  LET PriorYear = Year(YearStart(Today(),-1))

Thank you

14 Replies

Re: Point in Time Help

Do you have a date field instead of using Month and Year field? What is the format of date field?


Re: Point in Time Help

no i wish i did, only Month & year (Jan/ 2016)

Can i utilize those somehow?

Re: Point in Time Help

You can combine the two in your script to create a date field:

LOAD Month,


          Date(MakeDate(Year, Month, 1)) as Date

From Source....

And then may be something like this:

Sum({1<AcctNum = {'Sales'}, Month, Year, Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"}>} SalesAmount)


Re: Point in Time Help

That did not work, our Months are = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec' and our years are normal = 2013,2014,2015,2016

the solution did not work, any ideas?

Re: Point in Time Help

Would you be able to share a sample to help you better?


Re: Point in Time Help

I don't see an attach option?


Re: Point in Time Help

Using a Table object:

Dimension = Customer

Measure =  sum({1<SalesMonth = {'>=$(=vFirstMonthofYear), <=$(=vCurrentMonth )'}, SalesYear = ${=PriorYear}>} SalesAmount)

SET vFirstMonthofYear = 'Jan'

LET CurrentMonth = Month(Today())

LET PriorYear = Year(YearStart(Today(),-1))

// These are the current rates when the sales was made


Load * Inline [ 

Customer, SalesMonth,SalesYear,QTR,SalesAmount,SalesCurrencyType

A,Jan, 2014,Q1, 100.00, CAD

B,Feb, 2014,Q1, 200.00, CAD

C,Mar, 2014,Q1, 300.00, CAD

A,Apr, 2014,Q2, 400.00, CAD

B,May, 2014,Q2, 500.00, CAD

C,Jun, 2014,Q2,    600.00, CAD

A,Jul, 2014,Q3, 700.00, CAD

B,Aug, 2014,Q3, 800.00, CAD

C,Sep, 2014,Q3, 900.00, CAD

A,Oct, 2014,Q4, 1000.00, CAD

B,Nov, 2014,Q4, 1100.00, CAD

C,Dec, 2014,Q4, 1200.00, CAD

A,Jan, 2015,Q1, 1130.00, EUR

B,Jan, 2015,Q1, 1400.00, GBP

C,Feb, 2015,Q1, 1500.00, EUR

A,Feb, 2015,Q1, 1600.00, GBP

B,Mar, 2015,Q1, 1700.00, DKK

C,Mar, 2015,Q1, 1800.00, EUR

A,Mar, 2015,Q1, 1900.00, GBP

B,Apr, 2015,Q2, 2000.00, EUR

C,Apr, 2015,Q2, 2100.00, GBP

A,May, 2015,Q2, 2200.00, EUR

B,May, 2015,Q2, 2300.00, DKK

C,May, 2015,Q2, 2400.00, GBP

A,Jun, 2015,Q2, 2500.00, EUR

B,Jun, 2015,Q2, 2600.00, GBP

C,Jul, 2015,Q3, 2700.00, GBP

A,Jul, 2015,Q3, 2800.00, EUR

B,Aug, 2015,Q3, 2900.00, EUR

C,Aug, 2015,Q3, 3000.00, GBP

A,Sep, 2015,Q3, 3100.00, EUR

B,Sep, 2015,Q3, 3200.00, GBP

C,Oct, 2015,Q4, 3300.00, EUR

A,Oct, 2015,Q4, 3400.00, GBP

B,Nov, 2015,Q4, 3500.00, EUR

C,Nov, 2015,Q4, 3600.00, GBP

A,Dec, 2015,Q4, 3700.00, EUR

B,Dec, 2015,Q4, 3800.00, GBP

C,Jan, 2015,Q1, 3900.00, EUR

W,Dec, 2015,Q4, 3700.00, EUR

Y,Dec, 2015,Q4, 3800.00, GBP

Q,Jan, 2015,Q1, 3900.00, EUR

A,Jan, 2016,Q1, 530.00, EUR

B,Jan, 2016,Q1, 400.00, GBP

C,Feb, 2016,Q1, 500.00, EUR

A,Feb, 2016,Q1, 600.00, GBP

B,Mar, 2016,Q1, 700.00, DKK

C,Mar, 2016,Q1, 800.00, EUR

A,Mar, 2016,Q1, 900.00, GBP

B,Apr, 2016,Q2, 100.00, EUR

C,Apr, 2016,Q2, 100.00, GBP

A,May, 2016,Q2, 200.00, EUR

B,May, 2016,Q2, 300.00, DKK

C,May, 2016,Q2, 400.00, GBP

A,Jun, 2016,Q2, 500.00, EUR

B,Jun, 2016,Q2, 600.00, GBP

C,Jul, 2016,Q3, 700.00, GBP

A,Jul, 2016,Q3, 800.00, EUR

B,Aug, 2016,Q3, 900.00, EUR

C,Aug, 2016,Q3, 700.00, GBP

A,Sep, 2016,Q3, 1500.00, EUR

B,Sep, 2016,Q3, 1200.00, GBP

C,Oct, 2016,Q4, 1300.00, EUR

A,Oct, 2016,Q4, 1400.00, GBP

B,Nov, 2016,Q4, 1500.00, EUR

C,Nov, 2016,Q4, 1600.00, GBP

A,Dec, 2016,Q4, 1700.00, EUR

B,Dec, 2016,Q4, 1800.00, GBP

C,Jan, 2016,Q1, 1900.00, EUR

W,Dec, 2016,Q4, 1700.00, EUR

Y,Dec, 2016,Q4, 1800.00, GBP

Q,Jan, 2016,Q1, 1100.00, EUR];

Honored Contributor III

Re: Point in Time Help


Click on "Use advanced editor" in the top right.

Click on "Attach" in the bottom right.

May you live in interesting times!

Re: Point in Time Help

That is the issue, there is no  link "Use Advance editor" in the top right I wish you could see my screen.....