Qlik Community

Qlik Sense App Development

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

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

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?

deerhunter
Contributor

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,

          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
Contributor

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?

deerhunter
Contributor

Re: Point in Time Help

I don't see an attach option?

deerhunter
Contributor

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

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
Honored Contributor III

Re: Point in Time Help

Reply.

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

Click on "Attach" in the bottom right.

May you live in interesting times!
deerhunter
Contributor

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.....