topic Re: Point in Time Help in App Development

Point in Time Help

deerhunter — Mon, 25 Jul 2016 22:34:13 GMT

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

Re: Point in Time Help

sunny_talwar — Mon, 25 Jul 2016 23:23:12 GMT

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

deerhunter — Mon, 25 Jul 2016 23:43:36 GMT

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

Can i utilize those somehow?

Re: Point in Time Help

sunny_talwar — Tue, 26 Jul 2016 11:09:45 GMT

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)

Re: Point in Time Help

deerhunter — Wed, 24 Aug 2016 19:33:39 GMT

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

sunny_talwar — Wed, 24 Aug 2016 19:54:21 GMT

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

Re: Point in Time Help

deerhunter — Thu, 25 Aug 2016 14:40:32 GMT

I don't see an attach option?

Re: Point in Time Help

deerhunter — Thu, 25 Aug 2016 14:59:59 GMT

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

Re: Point in Time Help

oknotsen — Thu, 25 Aug 2016 16:08:55 GMT

Reply.

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

Click on "Attach" in the bottom right.

Re: Point in Time Help

deerhunter — Thu, 25 Aug 2016 21:30:57 GMT

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

Re: Point in Time Help

sunny_talwar — Fri, 26 Aug 2016 00:11:53 GMT

Check this link:

Uploading a Sample

Re: Point in Time Help

oknotsen — Fri, 26 Aug 2016 06:40:52 GMT

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

Re: Point in Time Help

sunny_talwar — Fri, 26 Aug 2016 10:03:23 GMT

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)

Re: Point in Time Help

deerhunter — Fri, 26 Aug 2016 13:37:15 GMT

Your the best, thank you

Re: Point in Time Help

sunny_talwar — Fri, 26 Aug 2016 14:06:30 GMT

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