Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Do you have a date field instead of using Month and Year field? What is the format of date field?
no i wish i did, only Month & year (Jan/ 2016)
Can i utilize those somehow?
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)
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?
Would you be able to share a sample to help you better?
I don't see an attach option?
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];
Reply.
Click on "Use advanced editor" in the top right.
Click on "Attach" in the bottom right.
That is the issue, there is no link "Use Advance editor" in the top right I wish you could see my screen.....