sum(value)>0 filter in pivot table makes problem

[rajaiqliksense]

Dear friends,

Im new to the qliksense.

Please check below scenario, where I use table1 as a source.

In pivot table need to aggregate value based on Ledger Name, and need to show the ledger name with only positive output and ignore 0 value ledgers.

table 1

Group           Ledger Name          Value

Sundry creditors      xyz1            1000

Sundry creditors      xyz1            -1000

Sundry creditors      xyz1            -10

Sundry creditors      xyz2            100

Sundry creditors      xyz2            -100

Sundry creditors      xyz3            100

pivot output 

Group                                ledgername                       value

Sundry creditors               total                                    100

Sundry Creditors              xyz3                                     100                      

While grouping we would get value as xyz1 as -10,  xyz2 as 0 and xyz3 as 100. so my output would be xyz3 as 100 only.

I've tried below expression, which would filter only independent value.

SUM({<VOUCHERDATE={"<=$(=$(Vprev1))>=$(=$Vprev1fy)"},ACTUAL1={">=0"},FINYEAR={"201617"}>}ACTUAL1)

in above expression i'm applying date & year filter and then VALUE>0 filter to get actual.

please help

[Anil_Babu_Samineni]

May be this?

Sum({<Value = {"=Sum(Value)>0"}>} Value)

Or

SUM({<VOUCHERDATE={"<=$(=$(Vprev1))>=$(=$Vprev1fy)"}, Value = {"=Sum(Value)>0"}, FINYEAR={"201617"}>}ACTUAL1)

[rajaiqliksense]

Hello Friend,

Thanks for the reply again.

i've tried this one, but its providing VOUCHERDATE wise values ( after applying value>0 condition).

But i need ledgername wise sum(value)>0 figures.

SUM({<VOUCHERDATE={"<=$(=$(Vprev1))>=$(=$Vprev1fy)"}, Value = {"=Sum(Value)>0"}, FINYEAR={"201617"}>}ACTUAL1)

something like this,

IF(MATCH(GNAME, 'Sundry Creditors'),
IF(MATCH(FINYEAR,'201617'),
IF(MAX(AGGR(SUM(ACTUAL1), ACNAME1))>0, SUM(ACTUAL1),0))).

but its not working.

Kindly help.

Thanks.

[rajaiqliksense]

Hello Again,

I'm able to achieve my requirement with below expression (i), but financial year filter (ii) is not working at pivot, why is it so?

part (i)

IF(MATCH(GNAME,'Sundry Creditors'),
IF(MAX(AGGR(SUM(ACTUAL1), ACNAME1))>0, SUM(ACTUAL1),0))

part (ii)

IF(MATCH(GNAME,'Sundry Creditors'),
IF(MATCH(FINYEAR,'201617'),
IF(MAX(AGGR(SUM(ACTUAL1), ACNAME1))>0, SUM(ACTUAL1),0)))

Please help.

Thanks.