Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
charlotte_qvw
Partner - Creator
Partner - Creator

Set analysis for max week in a month

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

13 Replies
charlotte_qvw
Partner - Creator
Partner - Creator
Author

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)

charlotte_qvw
Partner - Creator
Partner - Creator
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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


charlotte_qvw
Partner - Creator
Partner - Creator
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Charlotte,

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

Oleg

charlotte_qvw
Partner - Creator
Partner - Creator
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Nope, can't see any attachments...

Not applicable


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

tresesco
MVP
MVP

May be like this?

PFA