topic Re: Setting Year value in a calculation in QlikView

Setting Year value in a calculation

Anonymous — Wed, 04 Dec 2013 16:40:36 GMT

I am creating a dashboard on a new sheet within my qliktech report which will show amongst other things Sales values, Year to date sales and margins etc.

I am using the following to calculate the margin:-

if (wh<>'EN',((Sum(if (linetype='Invoiced',(val),0)))-(Sum(if (linetype='Invoiced',(cost),0))))/(Sum(if (linetype='Invoiced',(val)))),0)

This works fine when the current sales year (2014) is selected but when a user clears all selections or selects last year or the year before etc. the values obviously all change. I want the chart to only show Margin for this year irrespective of what year/month selections are made elsewhere....can this be done? I tried set analysis but that does not seem to work.

My year dimension is called FinYear - hopefully you could show me a working example of the above formula.

Thanks for your help - its greatly appreciated.

Re: Setting Year value in a calculation

jerem1234 — Wed, 04 Dec 2013 18:13:49 GMT

Using set analysis, selections can be ignored.

Something like this:

=sum({1}val)

will ignore all selections.

Something like this:

=sum({<Year=,Month=>}val)

will ignore selections made in the months of Year and Month.

For your expression, try adding these to your sums, something like this:

if (wh<>'EN',((Sum({<Year=,Month=>}if (linetype='Invoiced',(val),0)))-(Sum({<Year=,Month=>}if (linetype='Invoiced',(cost),0))))/(Sum({<Year=,Month=>}if (linetype='Invoiced',(val)))),0)

where Year, Month are your year and month fields.

However I can't be sure if this is your problem or not. If you could post a sample qvw that demonstrates the problem, that may help you get an answer faster. Also, how are you displaying this information? Bar chart, Straight table, Textbox?

Hope this helps!

Re: Setting Year value in a calculation

Gysbert_Wassenaar — Wed, 04 Dec 2013 18:14:31 GMT

Maybe something like this:

if (wh<>'EN',(Sum({<FinYear={2014},linetype={'Invoiced'}>}val)-Sum({<FinYear={2014},linetype={'Invoiced'}>}cost))/Sum({<FinYear={2014},linetype={'Invoiced'}>}val))

If you need more help please post a sample document that demonstrates the problem.

Re: Setting Year value in a calculation

Anonymous — Thu, 05 Dec 2013 15:20:33 GMT

Here is my QVW file. I have tried the set analysis but it appears not to work for me - I think I must be doing something wrong.

Essentially I want 2014 values in my GP chart and not values for previous years even if those years are selected...

Thanks for your help!

Re: Setting Year value in a calculation

Anonymous — Thu, 05 Dec 2013 15:21:35 GMT

Sample qvw file attached above...thanks for your help!

Re: Setting Year value in a calculation

Anonymous — Thu, 05 Dec 2013 15:21:51 GMT

Sample qvw file attached above...thanks for your help!

Re: Re: Setting Year value in a calculation

jerem1234 — Thu, 05 Dec 2013 15:59:00 GMT

See if attached is what you are looking for.

Hope this helps!

Re: Setting Year value in a calculation

Anonymous — Thu, 05 Dec 2013 16:02:20 GMT

Great - Thank you!!

This works as I want it!

Best Regards

Sagar