topic Re: Re: Re: HOW TO WRITE it in QlikView

HOW TO WRITE it

Wed, 02 Apr 2014 10:08:19 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 10:21:37 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 12:00:36 GMT

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.

Re: HOW TO WRITE it

Wed, 02 Apr 2014 12:12:48 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 12:30:02 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 12:39:39 GMT

Ok, give me a moment, i will try

Re: Re: HOW TO WRITE it

jagannalla — Wed, 02 Apr 2014 13:59:46 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:16:30 GMT

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

Re: Re: HOW TO WRITE it

jagannalla — Wed, 02 Apr 2014 14:23:10 GMT

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

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:30:58 GMT

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

Re: Re: HOW TO WRITE it

jagannalla — Wed, 02 Apr 2014 14:33:25 GMT

Can you share your file here.

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:38:08 GMT

Jagan,

sorry, but are you sure, that this part:

$(=Min({<NumINPDATE={">=$(vStart)<=$(vEnd)"}>}INPDATE))

will give result regarding dimension value?

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:41:42 GMT

if I change startdate to 2013/01/18,B should be 14 not 20.

Re: Re: HOW TO WRITE it

jagannalla — Wed, 02 Apr 2014 14:43:54 GMT

Dariusz Mielczarek,

Please check my attachments in above conversation, It matches the output result what Jack he is expecting. Let me know if you have any queries.

Thanks,

Jagan

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:47:07 GMT

yes. this date is ok. like the date for B is 2013/01/30

Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:49:44 GMT

Ok, i will if i only get new license

look:

$(=Min({<NumINPDATE={">=$(vStart)<=$(vEnd)"}>}INPDATE))

$(= )

In my oppinion it will give only one date point for entire SERNO poppulation. But Jack needs here 1 date for each SERNO....

Because each of SERNO may have different min INPDATE between start and end date

Re: Re: Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:52:11 GMT

Re: Re: Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:54:53 GMT

yes ,I should get the min date for each SERNO. I hope to use aggr ,but It don't work.

Re: Re: Re: HOW TO WRITE it

Wed, 02 Apr 2014 14:57:36 GMT

I have to go home now.

If there will be still no solution, i will try to do something more with this subject today evening (we have 17:00 now).

regards

Darek

Re: HOW TO WRITE it

male_carrasco — Wed, 02 Apr 2014 14:58:28 GMT

Try this

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