Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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,

11 Replies

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,

load *,

num(orderDate) as orderDateNum

..

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

Where vSelectedDate = Num(max(OrderDate))

Hope it helps.

Not applicable

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.

Not applicable

Re: Sum if field not starts with cm

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.

MVP
MVP

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.

erich_shiino
Honored Contributor

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

Not applicable

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 ?

MVP
MVP

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.

Not applicable

Re: Sum if field not starts with cm

Ok thanks, everything seems to work now

Not applicable

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

Community Browser