Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

29 Replies
Not applicable
Author

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
Author

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
Author

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
Author

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
Author

Ok, give me a moment, i will try

jagannalla
Partner - Specialist III
Partner - Specialist III

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
Author

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

jagannalla
Partner - Specialist III
Partner - Specialist III

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
Author

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