topic Set analysis Only() Question in QlikView

Set analysis Only() Question

Mon, 22 May 2017 22:08:23 GMT

Hello Qlik community,

I am trying to return different allocation amounts based on customer account selections. The majority of customer accounts have no allocations, but if they do, I need to return what percent is allocated, I have the following bit of code and it isn't doing what I want it to:

=Only({1<AllocatedSalesPartner={"$(vPartnerSelection)"}>} Allocations) * .01

This code returns an allocation amount if the customer account is allocated but that is all.

My goal with this code is to say return the Allocation if the AllocatedSalesPartner is equal to vPartnerSelection or return 1 if there is no allocation available. My data is laid out such that most customer accounts have no allocations associated with them and so that Allocations is null for most customers. I have played around with adding an if(isnull() beforehand but that didn't work.

Any suggestions? Thanks

Re: Set analysis Only() Question

ahaahaaha — Tue, 23 May 2017 06:54:31 GMT

Hi Alex,

It is good to see a fragment of the original data.

Regards,

Andrey

Re: Set analysis Only() Question

sunny_talwar — Tue, 23 May 2017 08:50:07 GMT

May be this?

Only({1<AllocatedSalesPartner={"$(vPartnerSelection)"}>} Alt(Allocations, 1)) * .01

Re: Set analysis Only() Question

Tue, 23 May 2017 13:54:31 GMT

Hey all, here's a sample of my data and my goal of what I'm looking for, I'm thinking that what I needed to change was the AllocationPercent but it is possible that set analysis may work using another expression.

My goal is to get to the Final Alloc Sales numbers below without having to use all of the additional columns:

In this chart table, CustomerNumber, and AllocatedSalesPartner are all dimensions, with everything else being expressions. I want to remove the AllocatedSalesPartner as a dimension, but when I do this is what happens:

For reference the formula for Final Alloc Sales is:

=if( [Net Sales] - [Direct Sales

(After Allocations)] >0, 0, [Direct Sales

(After Allocations)]) + [Allocations Add Back]

The formula for AllocationPercent is =Only({1<AllocatedSalesPartner={"$(vPartnerSelection)"}>} Allocations) * .01 and I used that instead of Simply Allocations * .01 because if I do that, It will not select the Allocation for the partner that is being selected.

I think that part of my probloem may be that Direct Sales (After Allocations) is

=if(Partner = AllocatedSalesPartner, [Net Sales] * [AllocationPercent],

if(isnull(AllocatedSalesPartner), [Net Sales]

))

When I remove the AllocatedSalesPartner dimension, Qlikview doesn't know how to check against the Partner selected..

Re: Set analysis Only() Question

Tue, 23 May 2017 15:14:59 GMT

Ended up solving my problem using separated sets, the "*" in between the clauses means include all sales where the SalesDate matches the selected date, the partner matches the partner selected, and there is no AllocatedSalesPartner

sum({$<SalesDate = {$(=concat(chr(39) & Date(Date, 'M/D/YYYY') & chr(39), ',') )}> * <Partner={"$(vPartnerSelection)"}> * -<AllocatedSalesPartner={"*"}> * <ProductCommissionGroup={"01","03"}> * <CustomerPriceGroup={"02","06"}>} InvoiceSales) +

sum({$<SalesDate = {$(=concat(chr(39) & Date(Date, 'M/D/YYYY') & chr(39), ',') )}> * <Partner={"$(vPartnerSelection)"}> * -<AllocatedSalesPartner={"*"}> * <ProductCommissionGroup={"01"}> * <CustomerPriceGroup={"25","28","30","32"}>} InvoiceSales)