Alternate States Expression

Tue, 20 Oct 2015 13:04:31 GMT

Dear QV Experts,

A straight table with three expressions,

Group1, which should display the Customer Sales of >10000 dollars [ For certain products of [yes]]

Group2 , which should display the Customer Sales of < 100 dollars [For certain products of [no]]

NetSales, which should display the Group1 - Group2.

The expectation is, If 10 customers are filtered based on the selection of Group1 then from that 10 customers the Group2 should be filtered.

I am getting Group1 figures correctly.

But Group2 should be from that EndCustomer if any Group2 sales < vInputAmtLess should display.

Can anyone please help/suggest me on this?

The expressions are below,

Group1:

if(
If(Metrics='Dollar',
num(sum({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[yes]<[CustomerCases] = {">=$(=vInputAmtMore)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[yes]<[CustomerEach] = {">=$(=vInputAmtMore)"}>} CustomerEach ),'#,##0.00')
)))>=$(=vInputAmtMore),
If(Metrics='Dollar',
num(sum({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[yes]<[CustomerCases] = {">=$(=vInputAmtMore)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[yes]<[CustomerEach] = {">=$(=vInputAmtMore)"}>} CustomerEach ),'#,##0.00')
))),)

Group2:

if(
If(Metrics='Dollar',
num(sum({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[yes]<[CustomerCases] = {">=$(=vInputAmtMore)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[yes]<[CustomerEach] = {">=$(=vInputAmtMore)"}>} CustomerEach ),'#,##0.00')
)))>=$(=vInputAmtMore),
If(Metrics='Dollar',
num(sum({[no]<[CustomerSales] = {"<=$(=vInputAmtLess)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[no]<[CustomerCases] = {"<=$(=vInputAmtLess)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[no]<[CustomerEach] = {"<=$(=vInputAmtLess)"}>} CustomerEach ),'#,##0.00')
))),)

NetSales:

if(
If(Metrics='Dollar',
num(sum({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[yes]<[CustomerCases] = {">=$(=vInputAmtMore)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[yes]<[CustomerEach] = {">=$(=vInputAmtMore)"}>} CustomerEach ),'#,##0.00')
)))>=$(=vInputAmtMore),

If(Metrics='Dollar',
num(sum({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>} CustomerSales )/1000-sum({[no]<[CustomerSales] = {"<=$(=vInputAmtLess)"}>} CustomerSales )/1000,'$#,##0.00'),
if(Metrics='Case',
num(sum({[yes]<[CustomerCases] = {">=$(=vInputAmtMore)"}>} CustomerCases )-sum({[no]<[CustomerCases] = {"<=$(=vInputAmtLess)"}>} CustomerCases ),'#,##0.00'),
if(Metrics='Each',
num(sum({[yes]<[CustomerEach] = {">=$(=vInputAmtMore)"}>} CustomerEach )-sum({[no]<[CustomerEach] = {"<=$(=vInputAmtLess)"}>} CustomerEach ),'#,##0.00')
))),)

Re: Alternate States Expression

swuehl — Tue, 20 Oct 2015 13:21:35 GMT

Please don't post multiple times with the same topic, it makes it hard to follow a discussion.

Alternate States

It's hard to answer your question because you don't give information about the full context, i.e. where you are using your expressions and how your data model looks like.

In general, you should be able to filter your records with something like

= Sum( {[no]<[CustomerSales] = {"<=$(=vInputAmtLess)"}, CustomerID = p({[yes]<[CustomerSales] = {">=$(=vInputAmtMore)"}>}) >} CustomerSales )

Re: Alternate States Expression

Tue, 20 Oct 2015 13:52:07 GMT

Thanks a lot. It worked for me.

topic Alternate States Expression in QlikView

Alternate States Expression

Re: Alternate States Expression

Re: Alternate States Expression