topic Re: Creating Pie chart on expression value in QlikView

Creating Pie chart on expression value

swiftfahad — Mon, 16 Apr 2018 03:53:02 GMT

Hi All,

I am using a table like this

VoucherID	VhrDate	Customer	PaidAmt	Type
v-123	01-01-2018	abc	5000	Paid
V-655	15-02-2018	xyz	4687	Paid
V-698	09-03-2018	eee	0	unpaid

Expression for PaidAmt is

=sum(receive_amt)

issue is the type column is also an expression field i.e =if(sum(receive_amt) = 0,'Unpaid','Paid');

now I want to show above table in pie chart to show the count of customer who paid/unpaid, something as in attached file

uacg0009 — Mon, 16 Apr 2018 06:18:05 GMT

Hi muhammad,

I think you can use column "Type" as dimension and use Count(distinct VoucherID) as expression.

Then you can make it.

Thanks.

Aiolos

petter — Mon, 16 Apr 2018 06:33:23 GMT

Qlik will not accept having an expression like this that is a so-called nested aggregation:

Count( Sum(PaidAmt)=0 )

but you are allowed to nest aggregations if the inner aggregation has a TOTAL quaifier:

Count( Sum(TOTAL PaidAmt)=0 )

However you need Aggr() to create the two dimension values in a calcuated dimension:

Create the bar chart and press the "calculated dimension" on the Dimension tab to create the unpaid/paid values:

=Aggr( If(Sum(PaidAmt)=0,'unpaid','paid') , Customer )

if the paid status should be calculated not just per Customer but per VoucherID, VhrDate and Customer then:

=Aggr( If(Sum(PaidAmt)=0,'unpaid','paid') , VoucherID, VhrDate, Customer )

Then add the expression

=Count(Sum(TOTAL PaidAmt) = 0)