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: 
Anonymous
Not applicable

Values Between Date!

Hi guys.

Now, i have a situation witch i belive not heva a solution, but i will ask...

There is way to make in script , sum a sales values between two dates...for example:

LOAD * INLINE

[STORE,DATE

001, 11/05/2016

002, 02/06/2016

003, 20/04/2016

002, 04/08/2016,

001, 11/09/2016,

003, 14/10/2016];

I have in other .qvd the sales by store and day, good, now i want make a logic in qlikview that will do this:

001, 11/09/2016 , $ 60000  --where this "$6000" is the sum between 11/05/2016 and 11/09/2016

002, 02/06/2016 , $ 3000    --where this "$ 3000" is the sum between 02/06/2016 and begin

003, 20/04/2016   $ 45790  --where this "$ 45790" is the sum between 20/04/2016 and begin

002, 04/08/2016,  $ 5000   --where this "$ 5000 " is the sum between 02/06/2016 and 04/08/2016

001, 11/05/2016,  $ 23456 --where this "$ 23456" is the sum between 02/06/2016 and begin

003, 14/10/2016  $ 78998  --where this "$ 78998" is the sum between 20/04/2016 and 14/10/2016

Understood?, I mean, i will sum sale between DATE and previous DATE found for that Store...

I can't think in nothig to resolve this solititation....

Thank you!!!

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Matheus,

                    Try this script:

[Store Dates]:

LOAD * INLINE

[STORE,DATE

001, 11/05/2016

002, 02/06/2016

003, 20/04/2016

002, 04/08/2016,

001, 11/09/2016,

003, 14/10/2016];

[Store Dates1]:

LOAD

STORE,

Date#(DATE) as ToDate,

If(STORE=Previous(STORE),Date(Date#(Previous(DATE)) +1),Date#( '01/04/2016') ) as FromDate

Resident [Store Dates] Order by STORE, DATE;

DROP Table [Store Dates];

This creates this table with proper intervals on which you can perform an IntervalMatch with extended syntax. I guessed at a begin date of 01/04/2016.

STORE FromDate ToDate
00101/04/201611/05/2016
00112/05/201611/09/2016
00201/04/201602/06/2016
00203/06/201604/08/2016
00301/04/201620/04/2016
00321/04/201614/10/2016

If your sales table has fields STORE and SALES then try an IntervalMatch along these lines:

IntervalMatch:

IntervalMatch (SALES,STORE) LOAD FromDate, ToDate, STORE resident [Store Dates1];

This will give you a synthetic key but don't be alarmed. This is to be expected and is not a problem. When QlikView creates a synthetic key as a result of a properly implemented IntervalMatch the synthetic key it creates is an efficient link table allowing intervals in one table to be associated with distinct values in another.

Good luck

Andrew

View solution in original post

3 Replies
Anonymous
Not applicable
Author

May be try with interval match ..

effinty2112
Master
Master

Hi Matheus,

                    Try this script:

[Store Dates]:

LOAD * INLINE

[STORE,DATE

001, 11/05/2016

002, 02/06/2016

003, 20/04/2016

002, 04/08/2016,

001, 11/09/2016,

003, 14/10/2016];

[Store Dates1]:

LOAD

STORE,

Date#(DATE) as ToDate,

If(STORE=Previous(STORE),Date(Date#(Previous(DATE)) +1),Date#( '01/04/2016') ) as FromDate

Resident [Store Dates] Order by STORE, DATE;

DROP Table [Store Dates];

This creates this table with proper intervals on which you can perform an IntervalMatch with extended syntax. I guessed at a begin date of 01/04/2016.

STORE FromDate ToDate
00101/04/201611/05/2016
00112/05/201611/09/2016
00201/04/201602/06/2016
00203/06/201604/08/2016
00301/04/201620/04/2016
00321/04/201614/10/2016

If your sales table has fields STORE and SALES then try an IntervalMatch along these lines:

IntervalMatch:

IntervalMatch (SALES,STORE) LOAD FromDate, ToDate, STORE resident [Store Dates1];

This will give you a synthetic key but don't be alarmed. This is to be expected and is not a problem. When QlikView creates a synthetic key as a result of a properly implemented IntervalMatch the synthetic key it creates is an efficient link table allowing intervals in one table to be associated with distinct values in another.

Good luck

Andrew

Anonymous
Not applicable
Author

Andrew, It worked...

I did like you said and it worked....But i dont got it how use intervalmatch, but i did by other way, because i needed sum sales values between date begin_and date_end, but with intervalmatch i dont get it...But anyway, it worked a lot...Thank you and thanks for all who help....