topic Re: Quarter Values in Chart in QlikView

Quarter Values in Chart

Anonymous — Wed, 06 Aug 2014 14:52:52 GMT

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..

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 14:59:02 GMT

In Front end in UI side

Write calculated dimension

Dimension:- QuarterEnd(Date)

Expression:- Sum(Amt)

Re: Quarter Values in Chart

Anonymous — Wed, 06 Aug 2014 15:01:13 GMT

Use

QuarterEnd(Date) as QEndDate

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 15:09:32 GMT

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)

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 15:13:13 GMT

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)

Re: Quarter Values in Chart

Anonymous — Wed, 06 Aug 2014 15:30:24 GMT

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..

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 16:27:11 GMT

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

Dimension:- QuarterEnd(Date)

Expression:- FirstSortedValue(Amt,-Date)

Then you get output:-

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 16:29:55 GMT

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

Re: Quarter Values in Chart

Anonymous — Wed, 06 Aug 2014 16:40:00 GMT

Hi,

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

Re: Quarter Values in Chart

its_anandrjs — Wed, 06 Aug 2014 16:45:33 GMT

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)

Re: Quarter Values in Chart

Anonymous — Wed, 06 Aug 2014 17:12:25 GMT

Thanks Anand it worked!!!