Hey guys!
I have this
“SUM_test_ave” is using “Weeks” and “testa9” is using “Weeks Inc” … I’m trying to multiply “sum_test_ave” * testa9, and my idea is that without care what combination I´m using when selecting the weeks from both filters, the calculation should get updated. However this is not happening… Does anyone know how to do this ?
@Allan_Valenciano, can you be more specific when you say sum_test_ave * testa9 is not working, are you getting errors or the result is incorrect. Can you share your calculation?
@DeepakS Thanks for answering… sure
Using Excel, this would be like 6,185.17 * 10.71% = 662.43. Which will get updated whenever I made a new selection on any of the weeks, however in QS, seems like I need to make a new filter having both weeks, but this is affecting the results of testa9 or sum_test_ave…
@Allan_Valenciano, you have created two separate visuals and two separate filters, is it because data is available in two different datasets? If that so, can you join these datasets?
@DeepakS is one visual, two separate filters using a common column named week_#, and one dataset. That´s why I don´t understand why is not letting me multiply the values… Is possible to do something like ifelse(sum_test_aver, use WEEKS) * ifelse(testa9, use weeks inc), just thinking out loud
Is one of your fields an aggregation and the other not?
Hi @Max, both are aggregations, sum_test_aver is an avg and testa99 is a sum. Diving deep I see that the issue is the Week´s filter, doesn´t matter if I select Weeks_Vol or Inc_Weeks, the result is not the desired. So my guessing is that it should be a way to take 6,185.17 and 10.71% as final results and multiply them, without the necessity of use filters…
I’ve built an example below that may provide a solution using 2 parameters, one for “Week” and another for “Week Inc”, each parameter then has an associated control on the sheet.
I then use the 2 parameters in the calculated fields and aggregate the values based on the weeks selected in each week control. Here’s my final analysis
The steps I took to create this are below:
Create 2 identical parameters “Weeks” and “WeeksInc” as follows (note - because we’ll be using the parameter in a calculated field we must define default values)
Add a control for each parameter as follows:
For my calculated fields containing sum and avg aggregations I use the following formulas:
avg2
avgif({apercentage},in(week,${WeeksInc}))
sum2
sumIf({ameasure},in(week,${Weeks}))
These only include the measure values in the aggregation if the week value is currently selected via the parameter control (achieved by using the in() function).
To perform the final calculation I simply then create a calculated field Total as
avg2*sum2
Let me know if this helps and provides a possible solution, if so, please mark this as a “Solution”.
Regards,
Andrew
@abacon you´re a genius! Thanks man, is working… and thanks for expanding my knowledge about the different ways to filter and control a report, using formulas…
