Get last week when this week has no data

[sifatnabil]

Hi all,

If today is Wednesday for example, and I want to get the full last week's revenue, I use this which works:

=money(sum({<YEARWEEK={'$(vYEARWEEK)'},Product={'ENERGY'}>} [REVENUE]),'£#,##0')

where vYEARWEEK=max(YEARWEEK)-1

However, for some products, there is no data for this week. Then the above set analysis returns the revenue from 2 weeks ago (as opposed to 1 week ago), which is wrong. How do I ensure it returns one week ago no matter what?

[martinpohl]

Hello,

I can't believe that QV gives you the revenue for some products 2 or more weeks ago.

In variable vYEARWEEK the result for today ist 201513

and this value is set in your setanalysis as yearweek definition

Regards

[Anonymous · Accepted solution]

Iam nit sure if set analysis is possible in where condition, but I would try

where vYEARWEEK=max({<youproductfield=>}YEARWEEK-1)

this should take the previous year regardless of any products