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