Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Let say I have a table like this
SalesDate SaleAmount
10/02/2010 55
11/03/2009 44
5/05/2011 33
2/04/2011 11
1/07/2011 3
5/11/2012 6
2/02/2012 18
1/01/2012 24
3/04/2012 10
1) How can I show total SalesAmount if SalesDate is less then 12 months old from todays date. In this case expression will read each record and add total sales if the date is not older then 12 months.
I want to show this is a text box.
The below expression does not work because it cannot read each salesdate individually and total it.
if(SalesDate > AddMonths(today(),-12),sum(SalesAmount),0)
please help
thanks
Hope this helps.
Can someone please explain this set analysis step by step.
=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)
thanks
Let me try explain your set analysis:
Hope this helps.
Regards,
tresesco
I haven´t tried this, so could be wrong but I would try it.
Hope this helps.
if(aggr(sum({<SalesDate={'>=$(=AddMonths(Today(),-12)'}>}SalesAmount), SalesDate) > X, 'do this', 'do else')
I´m assuming that you are using SalesDate as dimension in your chart.
May be you coul try to do this:
Calculate dimension:
If(SalesDate>= addmonths(today(),-12), SalesDate) //this returns only the dates since the prior year from today.
Expression:
aggr(sum(SalesAmount),SalesDate)
Good luck, let me know your results.
Regards,
Chema
Thanks for all the answers, I will try it out today and let you guys know.
I have been able to do simple set analysis and understand them. However this is bit complicated. What is the best place to learn set analysis, syntax. Do you need programming background in order to grasp the concept?
Hi,
Try this below link, hope it will help you to know basic set analysis concept.
Hope it helps
Mayil Thanks for the wizard.
Now please tell me how you will come up with below solution using this wizard step by step. Is it possible.
=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)
Hi,
1.Sum(SaleAmount) gives sum of all the sales amount
2. Max(SalesDate) give the maximum date which you select in your field for example , if you select 5/5/2011,6/5/2011,7/7/2011 in this selection, it gives 7/7/2011
3. AddMonths(startDate,n) function
Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.
Examples:
addmonths ('2003-01-29',3) returns '2003-04-29'
4. So =sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount) in this , for example
sum({<SalesDate = {'>=AddMonths(Max(7/7/2011), -12)'}> SaleAmount )
AddMonths(Max(7/7/2011), -12) gives the date 7/7/2010 ,i.e. previous year of same date
>= means, greater than or equal to 7/7/2010
so from 7/7/2010 to 7/7/2011 sales amount calculate and give result.
Hope it helps
Thanks Mayil for the answer.
I got the explaination part. Now for the syntax any clues, any tips how to organize and remember.
If i had 2 statement like this on two seperate text box.
Sales Amount store 1
=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)
Sales Amount Store 2
=sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)
How can subtract 1 from the 2 in seperate text box.
HI,
By practice you can remember easily..See the link which i mentioned in earlier post.That also help you for know more about set anlysis..
=Sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount) - sum({<SalesDate={'>=$(=AddMonths(Max(SalesDate),-12))'}>}SaleAmount)
Itself you give in text box ..But the result is szero only i think. because both are same expression.
If you use in straight table or pivot table in expression part, you can use column(2) - column(1). Which post give helps to u, can u mark as helpful answer.
Hope it helps,