Max over multiple columns

Q&A
, , ,
1

Hey I have multiple plans and I’m using parameters to filter each month by a different plan.
now I want to take the max each month of the plan and then take the max of all of them, something like below:

max(maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 1 and {release_name} = ${Jan}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 2 and {release_name} = ${Feb}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 3 and {release_name} = ${Mar}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 4 and {release_name} = ${Apr}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 5 and {release_name} = ${May}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 6 and {release_name} = ${Jun}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 7 and {release_name} = ${Jul}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 8 and {release_name} = ${Aug}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 9 and {release_name} = ${Sep}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 10 and {release_name} = ${Oct}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 11 and {release_name} = ${Nov}) ,

maxif({single_cycle_capacity},month>= ${StartMonth} and month<= ${EndMonth} and month = 12 and {release_name} = ${Dec}) )

but the max only takes one argument, is there a way to do that?

2

Hello @Carl_Chahine, if you are wanting to check across multiple partitions, I would recommend trying the maxOver function instead. This will allow you to find the max value across a specific grouping in your dataset. I’ll link documentation for the function below:

If you have any follow-up questions on implementing this, please let me know. Otherwise, please mark my response as the solution. Thank you!

3

I don’t want a max over, i want to see the max between different columns. so if i have a column called a and another b I want the max between a and b (for 2 columns i can use an ifelse statement but for 12 columns this doesn’t apply)

4

Hello @Carl_Chahine, I think to find a solution, we will likely need a way to reduce the number of columns you have that are returning the max values you want to calculate.

Basically, I think you will want to use an ifelse statement to determine which plan value, rather than the max, is being returned based on the month values, like you have above. Then I think you could use the maxOver function on that value and partition it by months, so for each month the plan value would be appropriate to that time period. Then you would put that inside of another maxOver function with an empty partition to get the total max. Something like this:

planByMonth = ifelse(month = 1 and {release_name} = ${Jan}, {single_cycle_capacity},
month = 2 and {release_name} = ${Feb}, {single_cycle_capacity},
etc...)

maxByMonth = maxOver({planByMonth}, [{month}], PRE_AGG)

maxOfMaxByMonth = maxOver({maxByMonth}, [], PRE_AGG)

Now if you want to maintain the 12 column format for your visual, you can keep the fields you have already created above and then just use this for your max of the month max values. I just don’t think there will be a way to get the max for the 12 separate columns built with the maxIf function, unless you write a calculated field that checks the value being returned by each function and compares then for greater than or less than.

5

Hello @Carl_Chahine, since we have not heard back from you, I will go ahead and mark my above response as the solution. If you have any further questions on this topic, please let me know!

6

Hey Dylan,
sorry for the late reply. and thanks for your support.
The solution you gave me does not specifically work for my use case, what I am doing is just using the max then ifelse statement. I opened a request with the QS team, hopefully we could have this functionality.

1 Like
7

Hello @Carl_Chahine, I appreciate the update. If you are able to achieve a solution that works better for your use case from Support, please follow-up with the process you implemented. Thank you!

8

is there a way to create a parameter which has values that are other parameters with functions? if so then i could do a work around for this.

9

Hello @Carl_Chahine, I am not sure I fully understand your question here. In order to better assist you, can you please post a new question in the community and provide some more information about the errors you are facing and what you mean by this last response. You can also link this topic to provide some relevant information. That will ensure you are at the top of the priority list for a response from one of our Quick Sight experts. I will archive this topic, and look forward to assisting you further. Thank you!