Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this?
Sum({<Value = {"=Sum(Value)>0"}>} Value)
Or
SUM({<VOUCHERDATE={"<=$(=$(Vprev1))>=$(=$Vprev1fy)"}, Value = {"=Sum(Value)>0"}, FINYEAR={"201617"}>}ACTUAL1)
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.
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.