Quarter Values in Chart

[Anonymous]

Hi All,

Have the below Date column and the amount for 2 years ie:

Date               Amt

01/30/2013     100

02/28/2013     200

03/31/2013     300

04/30/2013     400

05/31/2013     500

06/30/2013     600

07/31/2013     700

08/31/2013     800

09/30/2013     900

10/31/2013     1000

11/30/2013     1100

12/31/2013      1200  and so on...

i need to show in the bar chart the Quarter end date and their values for example the output should be same as below:-

Output:-

03/31/2013     300

06/30/2013     600

09/30/2013     900

12/31/2013      1200

Can any one help me on this..

[its_anandrjs]

In Front end in UI side

Write calculated dimension

Dimension:- QuarterEnd(Date)

Expression:- Sum(Amt)

[Anonymous]

Use

QuarterEnd(Date) as QEndDate

[its_anandrjs]

Consider this sample table and try with this in front end

Main:

LOAD Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') AS Date, Amt, Date(QuarterEnd(Date#(Date,'MM/DD/YYYY')),'MM/DD/YYYY') as QtrEnd;

LOAD * INLINE [

    Date, Amt

    01/30/2013, 100

    02/28/2013, 200

    03/31/2013, 300

    04/30/2013, 400

    05/31/2013, 500

    06/30/2013, 600

    07/31/2013, 700

    08/31/2013, 800

    09/30/2013, 900

    10/31/2013, 1000

    11/30/2013, 110

    12/31/2013, 1200

];

Dimension:- QtrEnd

Expression:- Max(Amt)

[its_anandrjs]

And another one in the load script try with that and load another resident table for the solution see the script below.

Main:

LOAD Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') AS Date, Amt, Date(QuarterEnd(Date#(Date,'MM/DD/YYYY')),'MM/DD/YYYY') as QtrEnd;

LOAD * INLINE [

    Date, Amt

    01/30/2013, 100

    02/28/2013, 200

    03/31/2013, 300

    04/30/2013, 400

    05/31/2013, 500

    06/30/2013, 600

    07/31/2013, 700

    08/31/2013, 800

    09/30/2013, 900

    10/31/2013, 1000

    11/30/2013, 110

    12/31/2013, 1200

];

Solution:

LOAD Distinct

QtrEnd,

Max(Amt) as QtrAmt

Resident Main

Group By QtrEnd;

And then in the Straight table take fields

Dimension:- QtrEnd

Expression:- Sum(QtrAmt)

[Anonymous]

Hi all,

The Quarter end function gives me addition of month 3 + Month 4+ month 5 (400 + 500+600)..so it adds every thing, but  i just need to display the June data ie 600

Hope this explanation is clear. Please help me out..

[its_anandrjs]

Then on front end write the calculated dimension with expression by use FirstSortedValue

Dimension:- QuarterEnd(Date)

Expression:- FirstSortedValue(Amt,-Date)

Then you get output:-

[its_anandrjs]

And on the load script back end if required

Main:

LOAD Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') AS Date, Amt, Date(QuarterEnd(Date#(Date,'MM/DD/YYYY')),'MM/DD/YYYY') as QtrEnd;

LOAD * INLINE [

    Date, Amt

    01/30/2013, 100

    02/28/2013, 200

    03/31/2013, 300

    04/30/2013, 400

    05/31/2013, 1500

    06/30/2013, 600

    07/31/2013, 700

    08/31/2013, 800

    09/30/2013, 900

    10/31/2013, 1000

    11/30/2013, 1100

    12/31/2013, 1200

];

Solution:

LOAD

QtrEnd,

FirstSortedValue(Amt,-Date) as QtrAmt

Resident Main

Group By QtrEnd;

And use fields

QtrEnd and QtrAmt

[Anonymous]

Hi,

Thanks for reply, do i need to use inline as well?? even i tried this expression its not working.

[its_anandrjs · Accepted solution]

On front end just use calculated Dimension or Expression no need of the Inline table it is for explanation in place of the inline table use your table. And if you are using the back end logic then i also provide that logic with script.

Dimension:- QuarterEnd(Date)

Expression:- FirstSortedValue(Amt,-Date)