topic Last week orders in QlikView

Last week orders

Thu, 24 Mar 2011 07:02:17 GMT

Hello Qlikviewers!

I'm trying to show all orders that were entered in the previous week by mtons, the used dimension is the sales department that book the orders. I used the below formula, but had no success:

EWoche = Week number in which the order was entered
EJahr = Year in which the order was entered
both were "loaded as" from the field "Erstellungsdatum" in the original format DD.MM.YYYY
Auftragsmenge in Tonnen = the numbers of tons booked in an order

I used the following formula but got no result:

Sum ({$<EWoche={Week(Today())-1)}>}[Auftragsmenge in Tonnen])

The set should pick the whole previous week, i.e. from Monday to Sunday, based on the current date resp. week.

I found a similar thread, but the solution was obviously wrapped in an qvw file which I couldn't open as I'm currently still using the personal edition.

I would really appreciate your help!

Many thanks and regards
Günter

Last week orders

tresB — Thu, 24 Mar 2011 08:08:28 GMT

just guessing its an issue with the SET expression, try this:




Sum ({<EWoche={'=$(Week(Today())-1)'}>}[Auftragsmenge in Tonnen])

Regards, tresesco

Last week orders

Thu, 24 Mar 2011 08:31:04 GMT

Hi Tresesco,

thank you very much for your quick reply. I tried your version but unfortunately it did not work:

The green versions were my attempts to go step by step. The first line gives the correct data for week 11 (which is the previous calendar week) but the following expressions won't. But you must be right - it is definitly just a small modification to the set expression that will do the trick. Any further hints? What about the year? I guess I will have to also consider the year later on as calendar weeks of course repeat each year.

regards
Günter

Last week orders

suniljain — Thu, 24 Mar 2011 09:26:16 GMT

pls Try Following

Sum(if(EWoche = week(Today())-1),[Auftragsmenge in Tonnen))

Last week orders

Thu, 24 Mar 2011 10:18:43 GMT

Hi Sunil,

thank you for your input - unfortunately it does not work, it still say "no data available" in my chart. Is there maybe something missing? The expression shows a red underline - see below picture:

Last week orders

suniljain — Thu, 24 Mar 2011 10:34:34 GMT

sum(IF(EWoche = Week(Today())-1,Auftragsmenge in Tonnen))

Last week orders

suniljain — Thu, 24 Mar 2011 10:34:41 GMT

sum(IF(EWoche = Week(Today())-1,[Auftragsmenge in Tonnen]))

Last week orders

Thu, 24 Mar 2011 10:53:26 GMT

Hi Sunil,

thank you very much - that one works perfectly! May I ask how I would add now the year to avoid showing orders that were entered in the same calendar week of the previous year? This whole set analysis system is still very confusing to me...

Many thanks again!

Regards
Günter

Last week orders

suniljain — Fri, 25 Mar 2011 11:30:45 GMT

Yo want to eliminate order that were entered into same claender week of last year.

Correct me if I am wrong ?.

Last week orders

Fri, 25 Mar 2011 11:32:54 GMT

Yes, this is what I want. In other words only the calendar weeks of the current year should show up.

Regards
Günter

Last week orders

suniljain — Fri, 25 Mar 2011 11:41:29 GMT

In Dimention ,

Write Following in Dimention Expression

if(Year = Year(Today()),Week)