topic Re: Sum all data to specific date in QlikView

Sum all data to specific date

egoziyan — Fri, 08 Feb 2013 22:40:10 GMT

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

Re: Sum all data to specific date

jagan — Fri, 08 Feb 2013 23:13:12 GMT

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.

Re: Sum all data to specific date

avastani — Sat, 09 Feb 2013 05:28:07 GMT

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

Re: Sum all data to specific date

Oleg_Troyansky — Sun, 10 Feb 2013 03:47:20 GMT

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.

Re: Sum all data to specific date

Sun, 10 Feb 2013 16:56:00 GMT

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

Re: Sum all data to specific date

Anonymous — Sun, 10 Feb 2013 17:51:03 GMT

hi,

Please fiend the attachment of applicaton and lt me know.

Regards

Re: Sum all data to specific date

egoziyan — Sun, 17 Feb 2013 22:09:14 GMT

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.