topic Re: Varience Expression in QlikView

Varience Expression

Mon, 19 Oct 2015 08:06:54 GMT

How could I create an expression for the variance that would solve this? I need to show it in the bar chart with Year-Quarter as Dimension and it has data up to 2010 totally i need to show 24 bars in the variance chart ,This is the equation we currently use in excel: for variance calculation (Q3 2015-Q3 2014)/Q3 2014. can any one help me on this?

Re: Varience Expression

miguelbraga — Mon, 19 Oct 2015 08:27:25 GMT

Hi John,

Could you please elaborate your problem? I'm not sure that I understood your needs.

Thanks,

Miguel

Re: Varience Expression

miguelbraga — Mon, 19 Oct 2015 08:58:17 GMT

I John,

See this thread:

current vs previous quarter sales

Hope it helps

Re: Varience Expression

Anonymous — Mon, 19 Oct 2015 09:16:28 GMT

Hi,

Didn't got Your query right. Can You please explain clearly what you want to obtain.

Thanks,

Bunny

Re: Varience Expression

Mon, 19 Oct 2015 09:21:57 GMT

Hi Miguel,

If you see image there in the dimension, I have taken the Fiscal quarter ,which displays all the quarters from starting year to latest year, now I want a bar chart with variance calculation in it, the calculation formula is

variance= (current quarter sales/previous year same quarter value) -1.

Hope you got it!.

Regards

John

Re: Varience Expression

Mon, 19 Oct 2015 09:33:19 GMT

Hi Bunny Thanks your Prompt response, Please find the explanation about the requirement on the above.

Re: Varience Expression

jagan — Mon, 19 Oct 2015 09:34:58 GMT

Hi John,

Try this expression

=(Sum(Sales)/Alt(Above(Sum(Sales), 4), 0)) - 1

Regards,

jagan.

Re: Varience Expression

miguelbraga — Mon, 19 Oct 2015 09:52:11 GMT

Please check the attached file:

Re: Varience Expression

miguelbraga — Mon, 19 Oct 2015 10:00:36 GMT

If you found usefull my binary qvw, please mark it as Usefull Have good job.

Re: Varience Expression

Tue, 20 Oct 2015 07:12:18 GMT

Sorry Braga Miguel , I didn't find the option to give useful , I have already given to Jagan

Re: Varience Expression

Tue, 20 Oct 2015 07:43:11 GMT

Hi Jagan mohan , I have used this in the chart expression and it is working fine. however when I select specific year it is not showing any data. could you Please help me on this why it is behaving like that.

regards

John !

Re: Varience Expression

jagan — Tue, 20 Oct 2015 10:45:45 GMT

Hi,

This expression will fetch based on the values in the chart, if you select Year you just have four quarters so you won't get values.

To achieve this you have to calculate in script and then load data.

Regards,

Jagan.

Re: Varience Expression

Tue, 20 Oct 2015 11:10:33 GMT

Thanks Jagan , I tried it at the script level but I didn't get the output as required, could you please guide me on this how do I get it done at back end script

Procedure I followed :

I have calculated the Current year sales and Last year sales as two different fields but the values are not matching with sales , means

I loaded the data directly from the excel sheet and I took the results for 2015 Q2 and 2014 Q2 , then I have calculated the Current year sales and Last year sales fields there values are not matching with the 2015 Q2 and 2014 Q2. because

I have ID, in the present year it may exists in the previous year and it may not exist in the previous year, lets say

ID 5 exist in the Previous year but not in the Current year at that time if user selects current year [2015 ] for ID 5 , year 2015 we didn't have sales In the current year but not in the previous year , on this scenario I failed. please help me on this how do I get it work.

Best regards

John