topic Get last week when this week has no data in QlikView

Get last week when this week has no data

sifatnabil — Wed, 01 Apr 2015 13:48:29 GMT

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?

Re: Get last week when this week has no data

martinpohl — Wed, 01 Apr 2015 14:00:12 GMT

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

Re: Get last week when this week has no data

Anonymous — Wed, 01 Apr 2015 14:07:16 GMT

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