11 Replies Latest reply: Jun 15, 2012 2:45 AM by thijsjan

Sum if field not starts with cm

Hi,

I want to sum the field 'price' if the field 'type' does not start with cm.

And I also want to sum a other field if the orderDate is between the chosen date and 10 weeks before the chosen date.

I tried to modify this =SUM({\$<Year={\$(=Only(Year)-1)}>} Sales) to make it work but I can't get it to work.

Kind regards,

• Re: Sum if field not starts with cm

Hi,

for sum of price, if the field does not start with cm.

try this,

=if(Wildmatch(Type,'*cm*'),0,Sum(price))

orderdate between the chosen date and 10 weeks before the chosen date

modify the script as,

num(orderDate) as orderDateNum

..

= Sum({<Year=, Quarter=, Month=, orderDateNum= {'>=\$(=Num(WeekStart(vSelectedDate, -10))) <= \$(=vSelectedDate)'}>}Sales)

Where vSelectedDate = Num(max(OrderDate))

Hope it helps.

• Re: Sum if field not starts with cm

Instead of >=\$(=Num(WeekStart(vSelectedDate, -10))), you could also simply use >= \$(=vSelectedDate)-70. Makes it even more simple.

• Re: Sum if field not starts with cm

=if(Wildmatch(Type,'*cm*'),0,Sum(price))

this worked:

=SUM(if(Wildmatch(Factuurnummer,'CN*'),0,Invoerbedrag))

Couldn't get the first one to work.

And why do I need year,quarter,month and orderdatenum?

I thought about something like this:

date>=Date(date -70)&&<=date

But I don't understand the syntax for putting it in a expression.

• Re: Sum if field not starts with cm

HI,

Try this expression

=Sum({<Factuurnummer-={'*cm*'}, OrderDate={'>=\$(=WeekStart(Max(OrderDate), -9))<=\$(=Max(OrderDate))'}>} Invoerbedrag)

Hope this helps you.

Regards,

Jagan.

• Re: Sum if field not starts with cm

Hi, I would just adapt jagan's solution a little bit.

I'd use double quotes to make QV interpret it as a search and I would use only one * at the end to make it necessarily start with CM.

With *CM* QV would look for CM at any point of the script and not just the beginning.

=Sum({<Factuurnummer-={"cm*"}, OrderDate={'>=\$(=WeekStart(Max(OrderDate), -9))<=\$(=Max(OrderDate))'}>} Invoerbedrag)

Hope it helps,

Erich

• Re: Sum if field not starts with cm

Hi,

It doesn't work but this seems to work:

=Sum({<~Factuurnummer ={"cm*"}>} Invoerbedrag)

Now I only need to get the last part working:

=WeekStart(Max(OrderDate), -9) returns the right date, but the full statement doesn't work.

I think because I use a other field orderWeek as the field they select, if i do orderWeek = {'*'} it seems to work.

but if i do :

=Sum({<OrderWeek = {'*'},Factuurnummer-={"cm*"},Year= {'*'}, OrderDate={'>=\$(=WeekStart(Max(OrderDate), -9))<=\$(=Max(OrderDate))'}>} Invoerbedrag)

the syntax highlighting stops by the -= and if i change it to ~ also. So is it wrong ?

• Re: Sum if field not starts with cm

Hi,

In this expression replace "OrderDate" with your date field from which you arrived Week.  When we use -= it shows syntax error but it works, I think it is an issue in Qlikview.

=Sum({<OrderWeek= , Year=, Factuurnummer-={'*cm*'}, OrderDate={'>=\$(=WeekStart(Max(OrderDate), -9))<=\$(=Max(OrderDate))'}>} Invoerbedrag)

If you have month and Year filters then use

=Sum({<OrderWeek=, MonthFieldName=, Year=, Factuurnummer-={'*cm*'}, OrderDate={'>=\$(=WeekStart(Max(OrderDate), -9))<=\$(=Max(OrderDate))'}>} Invoerbedrag)

replace MonthFieldName with your Month field name.

Hope this helps you.

Regards,

Jagan.

• Re: Sum if field not starts with cm

Ok thanks, everything seems to work now

• Re: Sum if field not starts with cm

It seems that if I use

=Sum({<Factuurnummer-={"CN*"}>} Invoerbedrag)

it excludes everything where there is no Factuurnummer, but even if the Factuurnummer is empty I want to sum the Invoerbedrag

• Re: Sum if field not starts with cm

Hi,

Then try this way

=Sum(If( Factuurnummer LIKE 'CN*' OR IsNull(Factuurnummer) OR Len(Factuurnummer) = 0,

Sum({<Factuurnummer-={"CN*"}>} Invoerbedrag)))

Hope this helps you.

Regards,

Jagan.

• Re: Sum if field not starts with cm

Doesn't work either.