topic Re: Re: Re: Set analysis for max week in a month in QlikView

Set analysis for max week in a month

charlotte_qvw — Sun, 01 Sep 2013 13:50:24 GMT

Hi,

I'd like to have a bar chart by month showing the value for the month, but only of the max week of 4 or 5 weeks in the month. To get the bar chart by month I use this set analysis -

sum({<[Fiscal Year Numeric]={$(=max([Fiscal Year Numeric]))},[RentCal Month]=>}[Rent Account Core Arrears])

But I need to add some set analysis in for [RentCal Week]. If I use max as I have for the Fiscal Year above, I only get one week in the bar chart which isn't what I want. I'd like one week per month.

I tried to use firstsortedvalue([RentCal Week], [RentCal Month]) but that hasn't worked. I think I need to nest my set analysis for max week inside the month somehow, but not sure how to do this.

Any help would be great,

thanks very much, Charlotte

Re: Set analysis for max week in a month

charlotte_qvw — Sun, 01 Sep 2013 15:22:40 GMT

I have resolved by using this answer to a previous question from swuehl. But any other answers welcome...

It seems to me that your maximum DATA per MES and PRODUTO might be static (not depending on selections), so you could add a flag to your data model (add these lines to your script):

LEFT JOIN (Calendario) LOAD

MES,

PRODUTO,

max(DATA) as DATA,

1 as MAXDATAFLAG

Resident Calendario group by MES,PRODUTO;

and use this as expression in your chart with dimensions MES and PRODUTO:

=sum({<MAXDATAFLAG={1}>} VALOR)

Re: Set analysis for max week in a month

charlotte_qvw — Sun, 01 Sep 2013 15:56:14 GMT

But in fact my solution above is not going to work because the week I display within the month should be the max week selected. That is if no week selected then - max week in month, otherwise use the week selected. But always display only one week within the month. Thanks for any advice

Re: Set analysis for max week in a month

Oleg_Troyansky — Sun, 01 Sep 2013 16:31:05 GMT

Charlotte,

you should always keep in mind that the Set Analysis condition cannot be sensitive to Dimension values, because it's being calculated globally once per chart.

In your case, the requirement is the following:

1. If Week number is selected, then show the selected week, or

2. Otherwise use the last week of each month.

Both selections can be expressed in a way that doesn't depend on the Chart Dimensions, so your goal is achievable.

I'd recommend creating a variable that stores the desired condition, and using the variable in Set Analysis as a Modifier. Something along the following lines (can't guarantee the exact syntax):

SET vWeekFilter = '=if(GetSelectedCount(Week) > 0, 'Week = {$(=max(Week))}', 'LastWeekFlag={1}') ';

(in this expression, if any selections of Weeks were made, we use the maximum available week, otherwise we use those weeks marked with the Flag=1)

The chart expression will look like the following:

=sum({<$(vWeekFilter)>} VALOR)

I'm preparing a new blog post on using variables in dynamically calculated expressions, stay tuned and check my blog side:

www.naturalsynergies.com/blog

Oleg Troyansky

www.masterssummit.com

Re: Re: Set analysis for max week in a month

charlotte_qvw — Mon, 02 Sep 2013 21:22:24 GMT

Hi Oleg,

Thanks very much. I read your blog too - really useful. However I can't get the syntax to actually work! I attach my file, based on the example in your blog. If you have a moment, be most grateful if you could have a look...

thanks

Charlotte

Re: Re: Set analysis for max week in a month

Oleg_Troyansky — Mon, 02 Sep 2013 22:07:13 GMT

Hi Charlotte,

I can't see your file - can you try attaching it again?

Oleg

Re: Re: Re: Set analysis for max week in a month

charlotte_qvw — Mon, 02 Sep 2013 22:15:10 GMT

Here it is... Let me know if that's not ok.

Re: Re: Re: Set analysis for max week in a month

Oleg_Troyansky — Tue, 03 Sep 2013 00:30:28 GMT

Nope, can't see any attachments...

Re: Set analysis for max week in a month

Tue, 03 Sep 2013 03:26:24 GMT

Hi Charlotte,

Try to use this formula for your sample QVW:

=sum(if(GetSelectedCount(Week) > 0,DateCounter,if(Week(MonthEnd(Date)) = week(Date),DateCounter,0)))

If to your formula in your first message, it becomes

=sum({<[Fiscal Year Numeric]={$(=max([Fiscal Year Numeric]))},[RentCal Month]=>}if(GetSelectedCount([RentCal Week]) > 0,[Rent Account Core Arrears],if(Week(MonthEnd(Date)) = week(Date),[Rent Account Core Arrears],0)))

I don't know what's the name of your DATE field, just replace the above Date with your date field's name.

Hope this help.

Herman

Re: Set analysis for max week in a month

tresB — Tue, 03 Sep 2013 04:30:39 GMT

May be like this?

PFA

Re: Set analysis for max week in a month

charlotte_qvw — Tue, 03 Sep 2013 05:38:44 GMT

Oleg, I don't understand why you can't see the file... I checked when I wasn't logged in and it was ok for me then too. Can you see tresesco's file below? But don't worry too much. I think I can maybe sell the customer the Last Week in Month Flag. Going to try today anyway!

Re: Set analysis for max week in a month

tresB — Tue, 03 Sep 2013 05:58:35 GMT

Hello Charlotte,

Did you try my earlier attached file?

Re: Set analysis for max week in a month

charlotte_qvw — Tue, 03 Sep 2013 06:02:49 GMT

just checking it out right now!

Re: Set analysis for max week in a month

charlotte_qvw — Tue, 03 Sep 2013 06:12:41 GMT

This looks really helpful. I need to translate it into the real world on site today. Will let you know. Thanks!