Sum all data to specific date

[egoziyan]

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

[jagan]

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]

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

[Oleg_Troyansky]

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.

[#5]

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

[Anonymous · Accepted solution]

hi,

Please fiend the attachment of applicaton and lt me know.

Regards

[egoziyan]

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.