topic AW:Re: AW:Last yr value in QlikView

Last yr value

sathishkumar_go — Thu, 20 Jan 2011 11:59:50 GMT

Hi,

In my table, i have Fiscalyrwk,FcMonth,FcQtr.

Example

FiscalYrwk FcMonth FcQtr Value

200852 200812 200804 500

200952 200912 200904 1000

201052 201012 201004 2000

Now I want,

In chart (Line chart)

Dimension -- FiscalyrWk

expression i want to show current yr value and last yr value (i.e 201052 -> 2000 as well 1000)

I know it is possible to pull once again the table and use Applymap. i want to know wheather setanalysis itself it is possible can do?

I am waiting for ur reply.....

-Sathish

AW:Last yr value

Thu, 20 Jan 2011 13:53:09 GMT

Hello Sathish,

i would try to use SET Analysis in your expressions (like this, assuming you have a field FiscalYr):


sum( { <FiscalYr= {'2009'} > } Value)
sum( { <FiscalYr= {'2010'} > } Value)

Next idea is to define and use variables like CurYear=Year(today()) and LstYear=Year(Today())-1

Regards, Roland

AW:Last yr value

Thu, 20 Jan 2011 14:43:36 GMT

thank you it works!

AW:Re: AW:Last yr value

Thu, 20 Jan 2011 15:37:39 GMT

Hello MaevaFromAix,

the sum() in my post above is only one (most popular) member of the aggregation functions like count(), ....The SET analysis resides between the {}-brackets. Now with count() as example count( {<Year='2010'>} ID). This is to count only IDs "from the year 2010". Within the <> you specify the exception(s) of the current selection. For the exam. expression above it is very independet what the user has (or has not) selected as Year.

Last yr value

marcohadiyanto — Thu, 20 Jan 2011 17:04:18 GMT

you can try this:

{$<Year={$(=Max(Year)-1)}>}

regards

AW:Last yr value

sathishkumar_go — Fri, 21 Jan 2011 06:42:03 GMT

Hi Roland,

Thanks for ur reply. But this will not helpful for me.

Here I will explain more what i want.

Please find the attachments file. In this file explains what i want exactly.

Regards

Sathish

Last yr value

marcohadiyanto — Fri, 21 Jan 2011 08:08:15 GMT

i had opened your qvw, you can put this in your 'LastYr Value' expression :

sum({$<Year={$(=Max(Year)-1)}>}Value)

AW:Last yr value

sathishkumar_go — Fri, 21 Jan 2011 09:38:04 GMT

HI Macro,

I think u r not understand what i want. ur expression will not help for me.

Because i am not considering max(Year). and also dimension is week.

Kindly check that appication (Pivot table)

-Sathish

AW:Re: AW:Last yr value

Fri, 21 Jan 2011 10:53:11 GMT

Hello Satish,

now I had the time to take a look at your exam. app. See my linechart, I am quite sure thats want you want to achieve.

Notes: I created a new (temp.) dim: calendar week. It is independent from any year. Without this dim it would be much more difficult. So think about creating such a field during load, if you need it more often. Second: take a look at the expressions where I used SET analysis to get the according year. Didn't use any vars for the first step

AW:Re: AW:Last yr value

sathishkumar_go — Fri, 21 Jan 2011 14:47:06 GMT

Hi Roland,

Thanks for ur reply. But i need same dimension. I understand some other dimension it will work but challenge is same dimension it is possible to write set analysis?

Regards

Sathish

AW:Re: AW:Re: AW:Last yr value

Sun, 23 Jan 2011 11:39:18 GMT

Hi Sathish,

sorry, but as far as I know it isn't possible using week as dim and showing the values for 200948 and 201048 above each other as you did in your workaround in your chart. Your Week(-Year) depends on the year, this is that the values depend also on the year and can't be shown above each other. So I would follow up your idea with creating these values during load and store them in seperate fields. Then you can use the Week(-Year) as dim.