If set analysis not working in chart

[Anonymous]

Problem:

Hi I'm trying to use an if statement for a measure in a bar chart but for some reason its not working. I tried the same statement in a table and it returns the desired results, but when I enter the same formula in the bar chart it returns a blank chart with the message "The selections generated no data for this chart". Any help would be much appreciated!

Dimensions: Year and VaccName

Measure Set Analysis Statement: =if(FcYear=Year, Sum(FcQty))

(the set analysis statement works in a table view, but not for a bar chart, why?)

[sunny_talwar]

Try this out may be

=Sum(If(FcYear=Year, FcQty))

[Anonymous]

Thanks sunny that seemed to have worked for me.

What is the difference between the two? Is it because in the first statement it compares the entirety of the two fields against one another, which returns a FALSE, whereas in yours....? (not sure how its read differently in your statement)

[sunny_talwar · Accepted solution]

I am doing a row level check in my expression because it is inside the Sum function.... so, before FcQty is summed, it checks if FcYear for that rows equals Year or not.

In yours you are summing FcQty when FcYear = Year based on your dimension... so if you VaccName as dimension... there multiple FcYear and Year for VaccName and the expression doesn't know what needs to be compared to what and it just fails.

Evene better approach to solve this is to create a flag in the script and use set analysis (if possible, i.e. if both FcYear and Year are available in the same table in the script)

LOAD FcYear,

     Year,

     If(FcYear = Year, 1, 0) as Flag

     ...

FROM ....;

and then just thus

Sum({<Flag = {1}>}FcQty)