topic Re: count and if in set analysis in QlikView

count and if in set analysis

ajsjoshua — Mon, 15 Feb 2016 05:56:23 GMT

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.

Re: count and if in set analysis

Gysbert_Wassenaar — Mon, 15 Feb 2016 17:57:59 GMT

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

Re: count and if in set analysis

HirisH_V7 — Mon, 15 Feb 2016 18:38:55 GMT

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

Re: count and if in set analysis

HirisH_V7 — Mon, 15 Feb 2016 18:46:16 GMT

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