Sum if field not starts with cm

[#1]

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,

[MayilVahanan]

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,

load *,

num(orderDate) as orderDateNum

..

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

Where vSelectedDate = Num(max(OrderDate))

Hope it helps.

[#3]

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

[#4]

Instead of

=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.

[jagan]

HI,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

[erichshiino]

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

[#7]

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 ?

[jagan]

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.

[#9]

Ok thanks, everything seems to work now

[#10]

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