Last yr value - Qlik Community

sathishkumar_go · ‎2011-01-20

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

Report Inappropriate Content · ‎2011-01-20

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

Report Inappropriate Content · ‎2011-01-20

thank you it works!

Report Inappropriate Content · ‎2011-01-20

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.

RR

marcohadiyanto · ‎2011-01-20

hi

you can try this:

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

regards

sathishkumar_go · ‎2011-01-21

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

marcohadiyanto · ‎2011-01-21

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

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

sathishkumar_go · ‎2011-01-21

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

Report Inappropriate Content · ‎2011-01-21

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

RR

sathishkumar_go · ‎2011-01-21

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