Set analysis for three month in bar chart

[sivasiri99]

Hi All,

In bar chart i need to show three month's data

If for a perticula data data is not present in previous month it should pic max date data of the month month

Thanks in advance.

Regards

Sivaram

[sunny_talwar]

Or just this:

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))", "$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))", "$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)

[sivasiri99]

Hi Sunny,

Thanks

If same day is present in last month i need to get it if not then only i need to get max day value

in my example 26/10/2016 is present in all the three months i should get it

if not present pick the max day of that month

[sumitjadhav]

Hi Siva Krishna,

According to my Understanding ,on That Basis i have developed the code for your issue,please let me know ,wheteher it matches with your case.

Please try the below Script,

data:

LOAD *,

MonthName(RECEIVE_DATE) as Monthyear;

LOAD REGION,

     COUNTRY,

     TOTAL_GL_ACCOUNT,

     RECEIVE_DATE

FROM

[path);

Join(data)

LOAD Monthyear,

date(max(RECEIVE_DATE),'dd-MM-yyyy') as Date1,

1 as Flag

Resident data

Group By Monthyear ;

Output:

Thanks & Regards,

Sumit Jadhav.

[sivasiri99]

Hi Sumit,

Thanks

Can you send me that  QVW file.

[sivasiri99]

Hi Sunny

Please go through it for you reference

Could you please help me out

Regards

Sivaram

[sunny_talwar]

Try this:

RangeSum(

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))"}>}TTL_GL_ACCOUNT),

If(Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT)),

If(Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)))

[sivasiri99]

Hi Sunny,

If i  selected  6th march 2017 it should be 6th Feb 2017 and 31th Jan 2017  because 6th Jan data is not available .

if selected day is present in previous month it should get else max day of previous month.

Regards

Sivaram.

[sunny_talwar · Accepted solution]

This should do it now

RangeSum(

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))"}>}TTL_GL_ACCOUNT),

If(Sum(TOTAL {<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT), Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT)),

If(Sum(TOTAL {<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT), Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)))