topic Set Analysis with dates in QlikView

Set Analysis with dates

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

i am creating an application that takes this last week details. (Fiscal Year Last Week).

When i hardcode in the last week being 2017-38 in set analysis, I will get the value.

But I dont want to hardcode; i want it to realise the fiscal week last week.

The first text box shows the figure using set analysis and hardcoded date.

The second box is what i am trying to achieve which is 0 at the moment

The third box is another step i am trying to do which is get the range between last week and 6 weeks previous.

I dont want to use MaxString as

1. it doesnt work for me (that, or i am writing it inorrectly)

2. I need the 'last week' figure to be able to use for third box expression.

Re: Set Analysis with dates

sunny_talwar — Fri, 06 Oct 2017 14:07:41 GMT

Try this

=Count( {<Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

[The Fiscal Year Last Week] = {'$(=MaxString([The Fiscal Year Last Week]))'}

[Weekly Record Count])

Re: Set Analysis with dates

bobbydave — Fri, 06 Oct 2017 14:45:06 GMT

That's all good using MaxString but I wanted to use that expression in my 3rd box when I was to say

The Last 6 Weeks - Last Week

and by using MaxString, it wont give me a value if i were to use it that way

Re: Set Analysis with dates

sunny_talwar — Fri, 06 Oct 2017 14:51:27 GMT

May be this

=Count( {< Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

[The Fiscal Year Last Week] = {"=Only({1} [The Fiscal Year Last Week]) <= Max(TOTAL [The Fiscal Year Last Week]) and Only({1} [The Fiscal Year Last Week]) >= (Max(TOTAL [The Fiscal Year Last Week])-36)"}

[Weekly Record Count])