Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis Only() Question

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

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

4 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi Alex,

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

Regards,

Andrey

sunny_talwar

May be this?

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

Not applicable
Author

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:

Capture.PNG

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:

Capture2.PNG

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

Not applicable
Author

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)