topic Re: Set Analysis on a Data Island in QlikView

Set Analysis on a Data Island

Tue, 03 Jul 2012 01:03:45 GMT

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))

Re: Set Analysis on a Data Island

chematos — Tue, 03 Jul 2012 16:04:59 GMT

Try this:

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

Re: Set Analysis on a Data Island

Tue, 03 Jul 2012 17:06:20 GMT

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.

Re: Set Analysis on a Data Island

chematos — Tue, 03 Jul 2012 17:13:03 GMT

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

Re: Set Analysis on a Data Island

swuehl — Tue, 03 Jul 2012 17:15:42 GMT

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

Re: Set Analysis on a Data Island

Tue, 03 Jul 2012 18:08:04 GMT

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.