Set modifiers not working when adding if condition

[#1]

Hi Guys,

I am facing a problem wherein i need to toggle between two metrics (Sum and Average) between a trend line chart.

Please find attached sample qvf and problem description below:

Filters: Year and prodcode. Total/Average filters from inline load script

1) Chart 1: simply has a trend line that gets affected by prodcode filter but not year filter. Year filter will only affect my KPI chart.

2) Chart2: Same trendline (not affected by Year filter but only prodcode filter) but with if conditions for totals and Average. I have created a inline function in script that has two measures  "Totals"  and "Averages" and I am coding my Chart2 based on the two.

In chart 2, when i click on a year filter it filters my total/average for that year which is not expected as i have used a set expression asking it not to be affected by year filter. (note: chart1 works fine. The problem seems to be with if condition)

Any pointers are appreciated!

[sunny_talwar · Accepted solution]

Can you try this:

If(Only({<Year>} Aggregation) ='Totals', Sum({<Year= >} Sales),

If(Only({<Year>} Aggregation) ='Averages', Avg({<Year=>} prodcode)))

[#3]

Thanks a lot! Works great.

Would you mind taking a moment to explain how this works?

Thanks,
sowmya

[sunny_talwar]

Just ignoring Year filter for Aggregation field as well. If I don't do that the left hand side of your if statement check will have null for years which are not selected making the comparison impossible. To add set analysis, I needed to use only function which is the equivalent of not having any function.