Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

egoziyan
New Contributor II

Sum all data to specific date

G'day

My data is

date product qty

1.1.13 prod-X 1

1.1.13 prod-Y 1

2.1.13 prod-X 1

3.1.13 prod-X -1

I want to calculate my invnentory for  2.1.13

the result should be

Prod-X 2

Prod-Y 1

my problem is that when I chose the date 2.1.13 I only get the invnetory for Prod-X and not Prod-Y because he had not data on the chosen date.

My formula is

sum({$<Date= {"<=$(=max(SA_DATE))"}>} CALC_QTY)

where SA_DATE is the date I select

Any help is appreicated

1 Solution

Accepted Solutions
shaik_basha
Contributor III

Re: Sum all data to specific date

hi,

Please fiend the attachment of applicaton and lt me know.

Regards

6 Replies
MVP
MVP

Re: Sum all data to specific date

HI,

Your expression is correct, but check the date formats in date field  and max(SA_DATE)

=sum({$<Date= {"<=$(=max(SA_DATE))"}>} CALC_QTY)

Regards,

Jagan.

avastani
Contributor II

Re: Sum all data to specific date

Why not create a running total table by product and whatever other dimensions you want to support in the script? would be a lot faster than set analysis.

LOAD

     monthstart(masterdate) as date,

     sum(qty) as inventoryCount,

     productid,

     dim1,..

RESIDENT fact

GROUP BY

     ...

you can create a composite key to rid of the synthetic that may result and do an AUTONUMBER on the composite key to gain in performance AUTONUMBER(monthstart(masterdate) & '_' & productid) AS key and have the same key in the Fact table

MVP
MVP

Re: Sum all data to specific date

assuming that you might have a lot of transactions in your inventory file, the idea to pre-calculate a running balance is certainly the best, in terms of performance.

To answer the specific question, and to clarify Jagan's answer - in Set Analysis, when comparing dates, you need to spcify exactly the same date format as used for the date field in question. On the other hand, the function max() is always returning a numeric value. Therefore, it's impossible to use unformatted result of max() as a selection value for a date field. So, the correct formula should be:

=sum({$<Date= {"<=$(=date(max(SA_DATE)))"}>} CALC_QTY)

assuming that the Date is formatted using the default date format.

Not applicable

Re: Sum all data to specific date

Hi Jagan,

      Sorry to intrept this thread. I am new to qlikview. Where i can download this? i didnt find any trial version of qlikview. Please help me.

Thanks,

gowtham

shaik_basha
Contributor III

Re: Sum all data to specific date

hi,

Please fiend the attachment of applicaton and lt me know.

Regards

egoziyan
New Contributor II

Re: Sum all data to specific date

Thanks you all for the help.

I am sorry it took me along time but I was stock with it and eventually I found my problem in my date calander.

Community Browser