topic Re: How to get Pivot Value in Bar chart having buckets in QlikView

How to get Pivot Value in Bar chart having buckets

chandan3535 — Wed, 06 Jan 2016 20:34:43 GMT

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.

Re: How to get Pivot Value in Bar chart having buckets

sunny_talwar — Wed, 06 Jan 2016 21:26:49 GMT

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))"},

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

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

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

Re: How to get Pivot Value in Bar chart having buckets

shawn-qv — Wed, 06 Jan 2016 22:59:52 GMT

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.

Re: How to get Pivot Value in Bar chart having buckets

chandan3535 — Thu, 07 Jan 2016 05:06:16 GMT

Thanks Sunny !!!