topic Re: Need help on calculated dimensions in QlikView

Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 11:25:16 GMT

Hi,

I need help on calculated dimension. I need to get max year in my calculated dimension and for that reason I have used the below expression:-

{Year=<=max(Year)>}Year

and also I need previous year as a calculated dimension:-

{Year=<=max(Year)-1>}Year

but it gives me an error.

please help me to solve this.

Thanks,

Sandip Ghosh

Re: Need help on calculated dimensions

sujeetsingh — Fri, 08 May 2015 11:28:36 GMT

How can dimension get calculated for the complete set of values .

I think for this you need to first get the desired filter in your script.

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 11:32:28 GMT

I have a year field and based on that I need to calculated Maximum year

Re: Need help on calculated dimensions

marcus_sommer — Fri, 08 May 2015 11:33:08 GMT

I'm not sure if you it really needed as dimension. Often set analysis within the expressions is a better approach:

https://community.qlik.com/search.jspa?q=current+vs+previous

Simply create YTD, moving totals and comparisons versus Year Ago

- Marcus

Re: Need help on calculated dimensions

tyagishaila — Fri, 08 May 2015 11:34:38 GMT

Hi Sandip,

You can try,

only(Year) & only(Year)-1

Let me know is it working or not.

Re: Need help on calculated dimensions

stigchel — Fri, 08 May 2015 11:35:51 GMT

Like this:

Aggr(Only({<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Year),Year)

Re: Need help on calculated dimensions

tyagishaila — Fri, 08 May 2015 11:39:21 GMT

Calculated Dimension,

=aggr(if(Year=only(Year),Year,''),Year)

=aggr(if(Year=max(Year),Year,''),Year)

and along with click option Suppress When Value is Null

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 11:51:18 GMT

I have three columns like this :-

Year expense Income

2015 123 1234

2014 123 345

2013 123 345

2012 234 3345

Now I want to show Current year (Which is 2015) and previous year as a dimension and expression as sum(expense) and sum(income).

Now how to do that thing? means what I have to do in calculated dimensions.

please suggest.

Re: Need help on calculated dimensions

Fri, 08 May 2015 11:57:16 GMT

We can achieve that using set analysis in a simple way.

Use:

sum({<Year = {2014,2015}>}expense) and sum({<Year = {2014,2015}>}income)

Thanks,

Singh

Re: Need help on calculated dimensions

Anonymous — Fri, 08 May 2015 12:02:29 GMT

You don't have to use calculated dimension. You can use separate expressions

sum({$<Year={"$(=year(today()))"}>} expense)

sum({$<Year={"$(=year(today())-1)"}>} expense)

Similar for income

But if you prefer calculated dimension, it is

aggr(only({<Year={"$(='<=' & year(today()) & '<=' (year(today())-1))"}>} Year), Year)

Re: Need help on calculated dimensions

tyagishaila — Fri, 08 May 2015 12:07:36 GMT

Go for this attachment,

Hope it will help.

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 12:32:16 GMT

Hi,

I have added My Excel sheet as a data and I need to use year as dimension and

sum(Average Expense Growth Rate) as expression and need to show latest two year data. So year will show as 2014 and 2014 as dimension and sum(Average Expense Growth Rate) as expression.

How to do that? Please suggest.

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 12:39:19 GMT

If I use you dimension use is:-

Aggr(Only({<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Year),Year)

in calculated dimension it is showing only 2014 in my bar graph.

I am attaching my excel sheet. Please use a bar graph.

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 12:49:09 GMT

I Want to use a bar graph, so what will be the dimension and expression? as I want use year as dimension and expression as sum(expense) and sum(income)

Re: Need help on calculated dimensions

Fri, 08 May 2015 13:02:57 GMT

Dear Sandeep,

Use the below code in your expression

({<Year={$(=max(Year)),$(=max(Year,2))}>}[Average Expense Growth Rate])

and Year as your dimension.

Thanks

Re: Need help on calculated dimensions

Anonymous — Fri, 08 May 2015 13:27:36 GMT

Sorry Sandip, I don't see any relations between the file you uploaded and the question you asked. There is no Year (or any date field), Income, Expense.

Are you sure you've uploaded the right file?

Re: Need help on calculated dimensions

ananyaghosh — Fri, 08 May 2015 13:30:37 GMT

Hi,

I need to create a bar graph and have to use year as dimension, but need to largest two year in dimension and have to use summation of Income and Expense as expression.

Please suggest what to do in dimension and expression?

thanks,

Sandip

Re: Need help on calculated dimensions

Anonymous — Fri, 08 May 2015 13:46:35 GMT

Better...

See attached. the first version is using calculated dimension. the second - set analysis in expressions.

Re: Need help on calculated dimensions

ananyaghosh — Mon, 11 May 2015 06:52:50 GMT

Hi,

My excel holds the year value from 2015 to 2011 and your dimension expression is based on current year. But if my excel holds the maximum year as 2014 or 2013 as maximum year value, then how can I pick the maximum value from my excel sheet?

Please help me out.

Re: Need help on calculated dimensions

ananyaghosh — Mon, 11 May 2015 07:32:22 GMT

Hi,

I have used your expression, but when I select a year from a list box , for exp: 2013, in the chart it just showing the year 2013.

But If I want to show 2013, 2012 and 2011 as year dimension in the chart when I have selected the year 2013, what I have to do in the expression?