Last week orders

[#1]

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

[tresesco]

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

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

Regards, tresesco

[#3]

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

[suniljain]

pls Try Following

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

[#5]

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:

[suniljain]

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

[suniljain · Accepted solution]

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

[#8]

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

[suniljain]

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

Correct me if I am wrong ?.

[#10]

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

Regards
Günter