How to get Pivot Value in Bar chart having buckets

[chandan3535]

Dear Team,

I have a requirement i want to create a bar chart from my Pivot chart where i am using calculated dimension fields.

In Pivot chart i am getting values by aggregating the dimensions and i want to achieve the same value in my Bar chart,In Bar chart i want to show value based on buckets and i cannot create bucket in back end as it is a dynamic bucket .

Please note i am doing aggregation in my dimension as i need to pick only those value only whose  Amount i.e. (Out_NetDBMTR) is not equal to zero

Attaching my QVW.

Any help would be greatly appreciated.

[sunny_talwar · Accepted solution]

Check this out:

Expression:

=If(Num(sum({<Posting_Date={"<=$(=max(Date))"},Due_Date=,

CustomerNo = {"=Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}Out_NetDBMTR)

+

sum({<Posting_Date={"<=$(=max(Date))"},Clearing_Date={">$(=max(Date))"},Due_Date=,

CustomerNo = {"==Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}Out_NetDBMTR))

<>0,

(sum({<Posting_Date={"<=$(=max(Date))"},

CustomerNo = {"=Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}Out_NetDBMTR)

+

sum({<Posting_Date={"<=$(=max(Date))"},Clearing_Date={">$(=max(Date))"},

CustomerNo = {"=Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}Out_NetDBMTR))

-

(sum({<Posting_Date={"<=$(=max(Date))"},Document_Type={'MR'},

CustomerNo = {"=Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}NetDBMTR)

+

sum({<Posting_Date={"<=$(=max(Date))"},Clearing_Date={">$(=max(Date))"},Document_Type={'MR'},

CustomerNo = {"=Aggr(RangeSum(Sum({<Posting_Date={'<=$(=Max(Date))'},Due_Date=>}Out_NetDBMTR), Sum({<Posting_Date={'<=$(=Max(Date))'},Clearing_Date={'>$(=max(Date))'},Due_Date=>}Out_NetDBMTR)), CustomerNo)<>0"}>}NetDBMTR)))

[shawn-qv]

The reason your pivot table is giving a different result is because you have an additional Calculated Dimension in the table which essentially filters out Customer No 100001.

For example, if you specifically select Customer No 100002 and 100003 you will get the same result in your chart.

For that reason, you'll need to modify the expression in your chart so that its filtering the same data.

S.

[chandan3535]

Thanks Sunny !!!