Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

HOW TO WRITE it

what is wrong with the expression(aggr is wrong):

sum({<inpdate={'<=$(=date($(saldate)))

>=aggr(min({<inpdate={'<=$(=date($(enddate)))>=$(=date($(startdate)))'}>}inpdate),SERIALNO)

'}>} SALES)

the third get the min date by serialno

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: HOW TO WRITE it

Try this. It has results you expected when startdate is 10.01, and also has 14 for B when startdate is 18.01 

I prepared 2 expressions. First works with variables (start, end). 2-nd expression doesn't use those variables. It is only sensitive for selection in INPDATE field.

I'am also thinking why you need enddate? What if for some start and end date there is no INPDATE for some SERNO?

I dont know why, but first expression will not exclude tghose cases. I think there may be something with date format ....

But 2-nd approach excludes it ....

Have fun with QV

regards

Darek

29 Replies
Not applicable

Re: HOW TO WRITE it

What do you like to achieve?

Sum of sales in some min date inside range from start- to end-date, which date (as I think) may be different for each SERIALNO, because there may be days without SALES on SERIALNO?

regards

Darek

Not applicable

Re: HOW TO WRITE it

I have two  data. one is buy with inpdate,another is sale with saledate. they are connected with SERIALNO. I want to get date that is   min({<inpdate={'<=$(=date($(enddate)))>=$(=date($(startdate)))'}>}inpdate) for every SERIALNO  ---- the date is the first date between startdate and enddate.----  saldate,startdate,enddate is three date that I input.

Not applicable

Re: HOW TO WRITE it

And your goal is to show sum(SALES) for that min date?

May you include printscreen of your data model?

i'am trying to understand what you would like to do.

Maybe the best way will be if you will share xls file with sample data and draft of chart you would like to setup...

regards

Darek

Not applicable

Re: HOW TO WRITE it

SERNOINPDATEBUYQTY
A2013/1/55
C2013/1/83
B2013/1/1067
C2013/1/183
A2013/1/204
B2013/1/307
A2013/2/22
B2013/2/81
C2013/2/202

ANOTHER IS SAL

SERNOSALDATESALQTY
A2013/1/53
C2013/1/83
B2013/1/106
C2013/1/188
A2013/1/209
B2013/1/306
B2013/2/58
A2013/2/1523
C2013/2/189

startdate--2013/01/10, enddate 2013/01/30  saldate 2013/02/20

I should get the three date as following---- min({<inpdate={'<=$(=date($(enddate)))>=$(=date($(startdate)))'}>}inpdate),

A2013/1/20~2013/2/20
B2013/1/10~2013/2/20
C2013/1/18~2013/2/20

final result ( salqty for A,B,C) between above date

A32
B20
C17
Not applicable

Re: HOW TO WRITE it

Ok, give me a moment, i will try

jagannalla
Valued Contributor III

Re: Re: HOW TO WRITE it

Hi,

After lot of changes, I got result what you are expecting. I had developed the app according to your date format, so lot of date conversation along with number format. Please try to convert logic according to your format or use straight forward. Please find the attached document for your reference.

LOAD SERNO,BUYQTY,Date#(INPDATE,'YYYY/MM/DD') as INPDATE,Num(Date#(INPDATE,'YYYY/MM/DD')) as NumINPDATE;

LOAD * INLINE [

    SERNO, INPDATE, BUYQTY

    A, 2013/1/5, 5

    C, 2013/1/8, 3

    B, 2013/1/10, 67

    C, 2013/1/18, 3

    A, 2013/1/20, 4

    B, 2013/1/30, 7

    A, 2013/2/2, 2

    B, 2013/2/8, 1

    C, 2013/2/20, 2

];

LOAD SERNO,SALQTY,Date#(SALDATE,'YYYY/MM/DD') as SALDATE,Num(Date#(SALDATE,'YYYY/MM/DD')) as NumSALDATE;

LOAD * INLINE [

    SERNO, SALDATE, SALQTY

    A, 2013/1/5, 3

    C, 2013/1/8, 3

    B, 2013/1/10, 6

    C, 2013/1/18, 8

    A, 2013/1/20, 9

    B, 2013/1/30, 6

    B, 2013/2/5, 8

    A, 2013/2/15, 23

    C, 2013/2/18, 9

];

LOAD Num(Date#(RangeDate,'YYYY/MM/DD')) as Flag;

LOAD * INLINE [

    RangeDate

    2013/01/10

    2013/01/30

];

LOAD Num(Date#(SaleDate,'YYYY/MM/DD')) as SaleDateFlag;

LOAD * INLINE [

    SaleDate

    2013/02/20

];

variables :

vStart : =Min({1}Flag)

vEnd : =Max({1}Flag)

- St. table with dim SERNO and exp as =Sum({<NumSALDATE={">=$(=Min({<NumINPDATE={">=$(vStart)<=$(vEnd)"}>}INPDATE))<=$(=Only(SaleDateFlag))"}>}SALQTY)

Hope it helps you!!!

Cheers!!

Thanks,

Jagan

Not applicable

Re: HOW TO WRITE it

Firstly ,thanks. Now my problem is the three dates(startdate,enddate,saldate) always change . It should input via variables.How to do it ?

jagannalla
Valued Contributor III

Re: Re: HOW TO WRITE it

Hi,

Please find the attachment, I had developed based on my default date format i.e. MM/DD/YYYY

Create three variables:

vStart: 01/10/2013

vEnd: 01/30/2013

vSaleDate: 02/20/2013

Exp in St. table

=Sum({<SALDATE={'>=$(=Min({<INPDATE={'>=$(vStart)<=$(vEnd)'}>}INPDATE))<=$(vSaleDate)'}>}SALQTY)

Hope it helps you.

Cheers!!!

Jagan

Not applicable

Re: HOW TO WRITE it

'}>}INPDATE))<=$(vSaleDate)'}>}SALQTY)---- it show the wrong expression.

Community Browser