HOW TO WRITE it

[#1]

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

[#2]

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

[#3]

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.

[#4]

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

[#5]

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

ANOTHER IS SAL

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

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),

A	2013/1/20~2013/2/20
B	2013/1/10~2013/2/20
C	2013/1/18~2013/2/20

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

A	32
B	20
C	17

[#6]

Ok, give me a moment, i will try

[jagannalla]

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

[#8]

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

[jagannalla]

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

[#10]

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