Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
hi,
Please fiend the attachment of applicaton and lt me know.
Regards
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.