Alternate States

Tue, 20 Oct 2015 12:04:47 GMT

Dear QV Experts,

A straight table with three expressions,

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

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

NetSales, which should display the Group1 - Group2.

The expectation is, From the Group1, 10 customers are filtered 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?

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

Gysbert_Wassenaar — Wed, 21 Oct 2015 13:56:35 GMT

Post a Qlikview document with enough data to create a working example. And post an excel file with the expected outcome.

Re: Alternate States

Wed, 21 Oct 2015 14:00:54 GMT

Sure Boss. Thanks.

topic Alternate States in QlikView

Alternate States

Re: Alternate States

Re: Alternate States