calculate the varaince of Amount having Year and Year-1 condition

[learnflower]

Hi all,

I need to calculate the varaince(difference) of a measure between the Year and Year-1 condition , The year is coming from the column called as InciDate .
I am extracting the Year from the InciDate using the cast(extract(Year,[InciDate]),varchar(4)) we are using this as the Parameter .

Please let me know how can i take the difference of a measure between the Year and Year-1 condition while i get the Year from the parameter

Please help me on this.

[Lynn]

Is your question how to figure out Year-1 or how to get the difference?

To get the prior year you can use the _add_years function:

cast(extract(Year,_add_years([InciDate],-1)),varchar(4))

Not really sure why you are casting to a varchar or exactly what you are doing with this expression.

[learnflower]

My question is how to get the difference if the Year is coming from prompt and we need to get the difference of a measure based on the year condition Year and Year-1.

Please guide me on this

[Lynn]

One way to do this is to set your filter to get rows from Year-1 (instead of Year) to get all the rows needed to determine the variance. Then use query items to bucket the measures by year.

For example, create a query item called [ThisYear] and set the expression to:

Code Select Expand


case
  when extract(Year,[InciDate]) = extract(Year,current_date) then [YourMeasure]
  else 0
end


Create another query item called [LastYear] and set the expression to:

Code Select Expand


case
  when extract(Year,[InciDate]) = extract(Year,_add_years(current_date,-1)) then [YourMeasure]
  else 0
end


Now you can subtract the two query items to calculate the difference.

There are other ways to go about it (e.g., separate queries for the two years), so others may chime in with further options. Good luck!

[MFGF]

This is probably the way I would do it too. Simple and effective. :)

MF.


Sent from my iPad using Tapatalk

[learnflower]

Thanks , I am getting the variance .

Can i add the Variance which i got with another Measure can this be done by any means please let me know.

[Lynn]

Are you having a specific problem using the new variance measure? You can certainly use it as you would any other measure. If the measure you want to add to the variance is in the same query just drag in a data item and create and expression to add the two together.

If the measure you want to add to the variance is in a different query you'll need to do a join of some sort and perform the addition in the new joined query.