topic Re: Calculated Dimension in QlikView

Calculated Dimension

Fri, 02 Jan 2015 05:50:39 GMT

I want to create last three month data based on selected month

Re: Calculated Dimension

amit_saini — Fri, 02 Jan 2015 05:57:37 GMT

Hi,

Try something like below:

<Month = {"$(=max(Month)-2)"}> , {$} = current selection, it is the default

Thanks,
AS

Re: Calculated Dimension

amit_saini — Fri, 02 Jan 2015 06:00:05 GMT

Also check this:

http://community.qlik.com/message/463894#463894

Thanks,
AS

Re: Calculated Dimension

Fri, 02 Jan 2015 06:03:37 GMT

hope attached file helps you

Re: Calculated Dimension

avinashelite — Fri, 02 Jan 2015 06:23:30 GMT

Find the solution below:

use like this : =if(SHIP_MONTH<=APPAC_SelectedMonth and SHIP_MONTH>=APPAC_SelectedMonth-3,SHIP_MONTH) in dimension

Re: Calculated Dimension

Fri, 02 Jan 2015 06:27:44 GMT

Hi Bhawna Gupta

i am not expecting this based on selected current month i should display selected month-1 and -2 in

Calculated Dimension

in expression i have another values

Re: Calculated Dimension

Fri, 02 Jan 2015 06:30:13 GMT

so in dimension itself you use this like

if(Month<=Max(Month) and Month>Max(Month)-3,Month)

Re: Calculated Dimension

avinashelite — Fri, 02 Jan 2015 06:33:12 GMT

Hi siva,

Did you check my attachment and calculated dimension which i have given in the previous post.

Re: Calculated Dimension

Fri, 02 Jan 2015 06:35:50 GMT

in calculated dimension use following expression

=if(match(Month,'$(=Max(Month))','$(=Max(Month)-1)','$(=max(Month)-2)'),Month)

and click ok and after that check suppress null value option in the same tab

hope above option will work for you

Re: Calculated Dimension

Fri, 02 Jan 2015 06:36:59 GMT

no need of max month dear

understand my problem on based on current selection of month in input box i need to change in chat i want to get -3 values

if i select 12 month i want to get nov oct sep data

i have two expression

Re: Calculated Dimension

Fri, 02 Jan 2015 06:45:42 GMT

{1 < YourMonthField ={'>=$(=only(YourMonthField )-2)<=$(=only(YourMonthField ))'}>}

YourMonthField is either an internal Serial ID for a Time Dimension or your regular monthfield in a listbox.

Re: Calculated Dimension

avinashelite — Fri, 02 Jan 2015 06:46:00 GMT

brother it works perfectly as you want, when i check you don't have data for all the months that why its showing no data , to show this i have created a list box in the app please check.

Re: Calculated Dimension

Fri, 02 Jan 2015 07:06:17 GMT

=COUNT({1 <TRANSPORT_MODE={'AIR'} ,SHIP_YEAR={'=$(APPAC_SelectedCurrentYear)'},SHIP_MONTH ={'>=$(APPAC_SelectedMonth-2)<=$(APPAC_SelectedMonth)'}>} SHIP_NO)

AND

=COUNT({1 <TRANSPORT_MODE={'SEA'} ,SHIP_YEAR={'=$(APPAC_SelectedCurrentYear)'},SHIP_MONTH ={'>=$(APPAC_SelectedMonth-2)<=$(APPAC_SelectedMonth)'}>} SHIP_NO)

Re: Calculated Dimension

Fri, 02 Jan 2015 07:19:17 GMT

if i select month jan 2014 i should get get dec 13 nov2013 oct2013

Re: Calculated Dimension

Fri, 02 Jan 2015 07:44:31 GMT

Exactly!, because you are not using a time dimension table AKA master calendar table. so you cannot roll over months that cross years that easy.

a SIMPLE time dimension would look like this:, so no matter the year and month, you just put MonthSerial instead of SHIP_MONTH and the calendar is linked via MonthSerial. There is a script I use. I will upload. for you.

TimeDimension:

LOAD * INLINE [

MonthSerial,Year,Month

1,2013,1

2,2013,2

3,2013,3

4,2013,4

5,2013,5

6,2013,6

7,2013,7

8,2013,8

9,2013,9

10,2013,10

11,2013,11

12,2013,12

13,2014,1

14,2014,2

15,2014,3

16,2014,4

17,2014,5

18,2014,6

19,2014,7

20,2014,8

21,2014,9

22,2014,10

23,2014,11

24,2014,12

];

load * INLINE [

Month,MonthName

1,Ene

2,Feb

3,Mar

4,Abr

5,May

6,Jun

7,Jul

8,Ago

9,Sep

10,Oct

11,Nov

12,Dic

];

Re: Calculated Dimension

avinashelite — Fri, 02 Jan 2015 07:48:36 GMT

try like in the calculated dimension

if(Month<Max(Month) and Month=>Max(Month)-3,Month)

and use the conditions as you need in the expression.

Re: Calculated Dimension

jyothish8807 — Fri, 02 Jan 2015 08:03:29 GMT

Hi Sivaraman,

Try this:

LOAD
     num(SHIP_MONTH) as SHIP_MONTH,
     TRANSPORT_MODE,
     SHIP_YEAR,
     SHIP_NO

FROM

(qvd);

Create a variable:

vSelectedmonth =GetFieldSelections(SHIP_MONTH)

now in chart:

exp:

count({<SHIP_MONTH={'$(vSelectedmonth)','$(vSelectedmonth)-1','$(vSelectedmonth)-2'}>}SHIP_NO)

Hope it works.

The issue is because in your data month is in 01,02,03 format wich should be converted to 1,2,3.

Regards

Re: Calculated Dimension

Fri, 02 Jan 2015 08:54:24 GMT

hi Jyothish

i have two exp one for air and sea

i cannot use in exp

Re: Calculated Dimension

jyothish8807 — Fri, 02 Jan 2015 09:19:47 GMT

You can add this part in you existing expression. It will work.

=count({<SHIP_MONTH={'$(vSelectedmonth)','$(vSelectedmonth)-1','$(vSelectedmonth)-2'},TRANSPORT_MODE={'AIR'}>}SHIP_NO)

=count({<SHIP_MONTH={'$(vSelectedmonth)','$(vSelectedmonth)-1','$(vSelectedmonth)-2'},TRANSPORT_MODE={'SEA'}>}SHIP_NO)

Hope it helps.

Regards

Re: Calculated Dimension

Fri, 02 Jan 2015 09:20:13 GMT

Here is the solution: I put a slider bar for the top N rolling period. Created a small solution for your time dimension, and show you how to place that range of dates in the expression.

now the expression loooks like follows: =COUNT({1 <TRANSPORT_MODE={'AIR'} ,YEAR_MONTH_SERIAL ={'>=$(=only(YEAR_MONTH_SERIAL)-HowMany)<=$(=only(YEAR_MONTH_SERIAL))'}>} SHIP_NO)