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: 
deerhunter
Creator
Creator

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
sunny_talwar

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

deerhunter
Creator
Creator
Author

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

Can i utilize those somehow?

sunny_talwar

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

LOAD Month,

          Year,

          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)

deerhunter
Creator
Creator
Author

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?

sunny_talwar

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

deerhunter
Creator
Creator
Author

I don't see an attach option?

deerhunter
Creator
Creator
Author

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

CurrentCustomers:   

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

oknotsen
Master III
Master III

Reply.

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

Click on "Attach" in the bottom right.

May you live in interesting times!
deerhunter
Creator
Creator
Author

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