Point in Time Help

[deerhunter]

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

[sunny_talwar]

Check this link:

Uploading a Sample

[oknotsen]

Are you actually opening the topic or are you trying to reply directly via your inbox?

[sunny_talwar]

New Script:

LET vEnd = Num(MonthName(AddYears(Today(), -1)));

LET vStart = Num(MonthName(YearStart(Today(),-1)));

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

CurrentCustomers: 

Load *,

  Num(Date#(SalesMonth & '-' & SalesYear, 'MMM-YYYY')) as DateField

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];

Dimension

Customer

Expression

Sum({1<DateField = {"$(='>=' & vStart & '<=' & vEnd)"}>} SalesAmount)

[deerhunter]

Your the best, thank you

[sunny_talwar]

Awesome

I am glad it finally worked. I would request you to close the thread if you got what you were looking for.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny