Need expression for end of month calculation

[#1]

Hi All,

Can any body help expression for below:

Table:

ID, Date.

If I choose Month July, I have to get total no of records where Date <= 31 July’ 2014.

If I choose Year 2013 and (no month chosen), I have get total no of records where Date <= 31 Dec 2013.

If I choose Quarter (Q1), I have to get total no of records where Date <= 31 March.

If I choose Q2, I have to get total no of records where Date <= 31 June.

Thanks,

Lakshmi.

[#2]

Hi All,

Any one help?

[JonnyPoole]

If you have a Date field it could be best to use that in your SET ANALYSIS:

In your chart , use an expression like this where 'Date' is a date field in your data model and vMaxDate is a variable with the formula:   =max(Date).

sum(  {$<Year=,Quarter=,Month=, Date= {"<=$(=date(vMaxDate))"}>}   Sales)

This will find out the maximum date in user's selection and give every data point that is <= that same date.  Year= and Quarter= and Month= ensures all the preceding dates are included.

[#4]

Hi Jonathan,

Sorry, the above is not working for me i want like this.

If I selected 2013 July month i want to count max date of July 2013 to oldest record.

EX : in 2013 for July month 23 date is highest so i want to count from 23/July/2013 to 31/12/2012 till this count. (below example i have data till 2012 if i have 2011, 2010 .... so on i want to count all.

one more thing is if i chose only month so it should take 2014 and selected month and max date to do count.

If I choose Month July, I have to get total no of records where Date <= 31 July’ 2014.

If I choose Year 2013 and (no month chosen), I have get total no of records where Date <= 31 Dec 2013.

If I choose Quarter (Q1), I have to get total no of records where Date <= 31 March.

If I choose Q2, I have to get total no of records where Date <= 31 June.

Thank you very much for your help,

Thank you

Lakshmi

[#5]

Hi All,

Any help please?????

[JonnyPoole]

Here is an example.

I cleaned up your data load with this syntax:  (where Temp is the name of the INLINE table load)

NoConcatenate

Data:

load

Count,date(date#(Date,'DD/MM/YYYY')) as Date,day(date#(Date,'DD/MM/YYYY')) as Day, month(date#(Date,'DD/MM/YYYY')) as Month, Year(date#(Date,'DD/MM/YYYY')) as Year

Resident Temp

where date(date#(Date,'DD/MM/YYYY')) <> '-';

drop table Temp;

Then i created a variable vMaxDate with this formula: 

=date(max(Date),'MM/DD/YYYY')

And the chart expression to bring back only dates earlier than the max date in the user's selection is:

= sum(  {$<Year=,Day=,Month=, Date= {"<=$(=date(vMaxDate))"}>}   Count)