How to display latest six month data in the graph??

[Anonymous]

Hi Experts

In my expression i am using

"sum(if(Date(month)>=Date(min(month)) and Date(month)<=Date(max(month)),Total))"  [includes Calendar object]

this will display the entine data between the min and max(allong with calendar object).

Now the deal is to display only the latest six month data so how can i proceed this changes in the above expression.

Many thanks in advance.

[ashkrit501]

Worth a try ?

"sum(if(Date(month)>=Date(max(month))-6 and Date(month)<=Date(max(month)),Total))"

[jagan]

Hi Madhu,

I think you have a date field in your datamodel, so you achieve this by using below expression

=Sum({<DateField={'>=$(=AddMonths(Max(DateField), -6))<=$(=Max(DateField))'}>} Sales)

AddMonths(Max(DateField), -6)  = This gives you the date by subtracting 6 months from the selected date.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi,

You'll also need to do some sorting of the months dimension, so that if you're showing last 6 months and the current month is before june the months wrap from dec round to jan. I did this by creating a MonthSort table linked to the Calendar table's month field, in which 12 columns read MonthSort1...12, as follows:

Month	MonthSort1	MonthSort2	MonthSort3	MonthSort4	MonthSort5	MonthSort6	MonthSort7	MonthSort8	MonthSort9	MonthSort10	MonthSort11	MonthSort12
1	1	12	11	10	9	8	7	6	5	4	3	2
2	2	1	12	11	10	9	8	7	6	5	4	3
3	3	2	1	12	11	10	9	8	7	6	5	4
4	4	3	2	1	12	11	10	9	8	7	6	5
5	5	4	3	2	1	12	11	10	9	8	7	6
6	6	5	4	3	2	1	12	11	10	9	8	7
7	7	6	5	4	3	2	1	12	11	10	9	8
8	8	7	6	5	4	3	2	1	12	11	10	9
9	9	8	7	6	5	4	3	2	1	12	11	10
10	10	9	8	7	6	5	4	3	2	1	12	11
11	11	10	9	8	7	6	5	4	3	2	1	12
12	12	11	10	9	8	7	6	5	4	3	2	1

You can then determine the startmonth, e.g. Nov for 6 months to April, and then add [MonthSort$(StartMonth)] to the Sort by expression field.

Jonathan

[#5]

Hi Madhu,

I suggest that you can create Rolling Month Number and from that you can use max month ID - 6 to display the last 6 months data in the graph.

Thanks,

Srini.

[Anonymous]

Thanks a lot yaar.. this is not reflecting the graph as well..

  "sum(if(Date(month)>=Date(max(month))-6"sum(if(Date(month)>=Date(max(month))-6

[Anonymous]

Hi Jagan

By going with this month is showing lastest 6 month but while selecting calendar object this is not working

[jagan]

Hi Madhu,

You can ignore the calendar object selections by using MonthField=, YearField= like this exclude all date field in expression

Ex:

=Sum({<YearField=, MonthField=, QuarterField=, WeekField=, DateField={'>=$(=AddMonths(Max(DateField), -6))<=$(=Max(DateField))'}>} Sales)

Hope this helps you.

Regards,

Jagan.