topic Hi in QlikView

Hi

Mon, 15 Aug 2011 07:18:14 GMT

Hi everybody;

I've a question which I have treid to solve for days. I would like to create a table which contains comperative sales quantity. Forexample; I calculate sale quantity for cuurent year as a Year To Date. Namely, Today is 15.08.2011 and I can calculate for current years until this date without any problem. There is no problem. But at the table to see sales change; I would like to bring previous sales quantity to another column as year to date. Namely 15.08.2010. And automotically everyday I want to see cumululative comperative sales quantity. (for Tomorrow; 16.08.2011 - 16.08.2010) I sturggle to bring previous years' value.

To reach this aim I've calculated this expression as follows:

sum({<sts_Fatura_Yil={2010},sts_Stok_Group_Durum={KATI},Yeartodate(today())-1),makedate(2010)>}sts_miktar)

In the expression the names which begins with sts_ expressions are field and they have been described truely by our IT tem's member.

Is there anyone to help me pls ?

Thanks in advance,

Re: Hi

IAMDV — Mon, 15 Aug 2011 09:41:10 GMT

Hi,

Have you tried usingthe Year field? I mean using the Max(sts_Fatura_Yil) -1 gives the previous year.

SUM({<sts_Fatura_Yil={$(=Max(sts_Fatura_Yil)-1)},sts_Stok_Group_Durum={KATI},Yeartodate(today())-1),makedate(2010)>}sts_miktar)

If this doesn’t work... please can you postsample QV document with scrambled data?

Thanks - DV

Re: Hi

swuehl — Mon, 15 Aug 2011 09:46:05 GMT

Hi,

within a set expression, between the "< >", you use field selections, which consists of a field name, an assignment operator (like =, but also e.g += possible) and an element set expression (please compare to the Help file).

In your expression, I think

Yeartodate(today())-1),makedate(2010)

is not a valid expression in that context.

Yeartodate returns a boolean value (and I believe true in most circumstance, except for eg. year change or script run and document opening differ significantly), makedate returns January 1st, 2010 in your case.

I am not sure what you were trying to achieve with that expression, if I would like to see sales for past year to date, I would probably try limiting a Date field (like an order date) like this:

=sum({<OrderDate= {">=$(=yearstart(addmonths(today(),-12)))<=$(=addmonths(today(),-12)) "}>} Sales)

(I am using dollar sign expansion to call QlikView functions inside the assignment to field). This should read be as:

Assign the date range (start of the year of today minus 12 Months, today minus 12 Months) to OrderDate as selection for the sum calculation).

You may need to clear other Fields of type Date within the set expression like,

=sum({<OrderDate= {">=$(=yearstart(addmonths(today(),-12)))<=$(=addmonths(today(),-12)) "}, Month=, Year=, Day= >} Sales)

Please have a look also to http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

I found this quite useful

Hope this helps,

Stefan

Re: Hi

Wed, 17 Aug 2011 06:41:59 GMT

Thanks for your kindly and fast help. Best Regards

Re: Hi

Wed, 17 Aug 2011 06:44:48 GMT

Thank you very much for your help Dear. It was kind of you. Best Regards