Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis on a Data Island

I am new to Qlikview and I cannot figure out how to solve this issue.  I have a data island and I want to calculate total payments from this data island.  I have a chart that has dimensions of vendor and year.   I am trying to figure out the expression to use to pull the correct payment totals.  The data island has a Payment Year and Payment Vendor.  So basically I want the payment total on each line to be the sum of all payments were Payment Year = the current row’s dimension year and Payment Vendor = the current row’s dimension vendor.  I have tried all the following combinations but none of them seem to work.  Any help would be greatly appreciated.

- sum({$<PaymentFiscalYear=FiscalYear,PaymentVendor=Vendor>}  Payments)

- sum({$<PaymentFiscalYear=P({$}FiscalYear),PaymentVendor= P({$}Vendor)>}  Payments)

- sum(if(PaymentFiscalYear=FiscalYear,if(PaymentVendor=Vendor,Payments)))

- sum(if(PaymentFiscalYear=FiscalYear and PaymentVendor=Vendor, Payments))

- sum({$<PaymentFiscalYear=P({$}FiscalYear),PaymentVendor=P({$}Vendor)>}  distinct if(PaymentFiscalYear=FiscalYear,if(PaymentVendor=Vendor,Payments)))

- sum({$<PaymentFiscalYear=P({$}FiscalYear),PaymentVendor=P({$}Vendor)>} if(PaymentFiscalYear=FiscalYear,if(PaymentVendor=Vendor,Payments)))

- sum({$<PaymentFiscalYear=P(FiscalYear),PaymentVendor=P(Vendor)>} if(PaymentFiscalYear=FiscalYear and PaymentVendor=Vendor,Payments))

- if(PaymentFiscalYear = only(FiscalYear) and PaymentVendor = only(Vendor),sum(Payments))

5 Replies
chematos
Valued Contributor II

Re: Set Analysis on a Data Island

Try this:

sum({$<PaymentFiscalYear={"=FiscalYear"},PaymentVendor={"=Vendor"}>} Payments)

Not applicable

Re: Set Analysis on a Data Island

Thanks for the response. I tried using this statement and few different variations of it, but none of them gave me any data.  Any other suggestions?  Thanks. 

chematos
Valued Contributor II

Re: Set Analysis on a Data Island

Wait, if you have PaymentFiscalYear and PaymentVendor as dimensions in your chart, you only have to make the sum:

Sum(Payments)

The sum() operation will add all the Payments grouping by opened dimensions in your chart.

hope this helps, may be you could upload your file

MVP
MVP

Re: Set Analysis on a Data Island

Have you tried not using a set expression but a simple if() statement in your sum, maybe like

=sum( if( PaymentFiscalYear=FiscalYear and PaymentVendor=Vendor, Payments))

As with any set expression, the set analysis won't consider the current row's dimension values, so I think that's one of biggest issues.

But maybe I am just not fully understanding your setting and requirements, could you maybe post a simple sample QV file? Upload is available in advanced editor.

Regards,

Stefan

Not applicable

Re: Set Analysis on a Data Island

Here is a small sample of the qlikview application I have issues with.  In the chart there is a column for Payments Total Spend.  I am trying to sum up the payments for a particular vendor and year.  So I need an expression that will sum the payments where PaymentYear = the dimension year and PaymentVendor = the dimension vendor.

Community Browser