count and if in set analysis

[ajsjoshua]

Hi all,

i am using the formula

=count(DISTINCT if(sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'INVOICE'},Year=,Month,Quarter=>}#InvNetSales) 

-  sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'CreditNote'},Year=,Month,Quarter=>}#InvNetSales))<0,Item)

i want to count the item whose invoice netsales - credit netsales value is lesser than zero.

[Gysbert_Wassenaar · Accepted solution]

Try something like Count({<Item={"=sum(..invoice..)-sum(..credit note..)<0"}>}Item). If you need more help please post a small qlikview document that illustrates the problem

[HirisH_V7]

Hi,

May be like this,

=if(sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'INVOICE'},Year=,Month,Quarter=>}#InvNetSales)
-
sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'CreditNote'},Year=,Month,Quarter=>}#InvNetSales)<0,
Count(Distinct Item) )

HTH,

Hirish

[HirisH_V7]

Hi,

Check this attached,

Data:
LOAD * INLINE [
    ITEM, Sales 1, Sales 2
    1, 100, 888
    2, 111, 22
    3, 21, 11
    4, 111, 1111
];

Front end:

HTH,

PFA,

Hirish