topic Need help on set analysis in QlikView

Need help on set analysis

Wed, 04 Apr 2012 14:29:17 GMT

Hi All,

I want to show a chart in which the dimension is the Department and my expressios would be

1.count[cost center id] --(for current month)

2.count([Cost Centre Id] --- (for previous Month]..

Here how can i capture the previous month value?? The user selcts the year and Month..

Can some one plz help me with the expression for previous month....

Need help on set analysis

Jason_Michaelides — Wed, 04 Apr 2012 14:40:55 GMT

This has been answered many many times on this forum before!!

One way (I haven't double-checked the syntax) is:

Create a variable vLastMonth and set to =Month-1

Create a variable vLastYear and set to =Year-1

Count({<Month={'$(vLastMonth)'}, Year={'$(vLastYear)'}>} [Cost centre Id])

This solution assumes a single year and month is selected by the users.

Hope this helps,

Jason

Re: Need help on set analysis

Wed, 04 Apr 2012 14:45:04 GMT

What happens if selected month is January?

You need to have a date table with a monthID field which is in date order. You can use the autonumber function to create a monthID. From there you can use the method above. Each fact record should link either via a date ID if you need to daily or just monthId if you only need to know the month. This will require a lookup when loading the data.

Need help on set analysis

Jason_Michaelides — Wed, 04 Apr 2012 14:54:05 GMT

You need to use addmonths()

Do a search for previous month or point in time etc etc. here's one that might be appropriate:

http://community.qlik.com/thread/34976

Re: Need help on set analysis

Wed, 04 Apr 2012 15:11:53 GMT

Hi,

My requiremnt is bit different even if I dnt make selection on any month.. I should be able to see the count(cost center Id] for the previous month as a second column.. How do I writ ethe expression...

Thanks

Re: Need help on set analysis

Wed, 04 Apr 2012 15:29:16 GMT

Hi,

Do anyone have any suggestions .....

Need help on set analysis

Jason_Michaelides — Wed, 04 Apr 2012 15:31:12 GMT

Maybe look at Above()

Need help on set analysis

Wed, 04 Apr 2012 15:39:50 GMT

Use the MonthID as suggested above and then use this as your expresion.

count({$<MonthID={"=(MonthID-1)"}>} ......... whaterver you are counting)

Re: Need help on set analysis

v_iyyappan — Wed, 04 Apr 2012 15:44:04 GMT

Hi,

Use the below expression in previous Month when u select the year

=Count({<Month={'$(vLastMonth)'}>}DISTINCT cost center Id)

vLastMonth = Month(AddMonths(Datefield,-1))

Regards,

Iyyappan

Need help on set analysis

Wed, 04 Apr 2012 15:49:12 GMT

This wil only work within a single year as if you have two June's in your dataset they will get included twice. You must have a sequential monthID. For example where monthId is the first field.

1 Dec 2011

2 Jan 2012

3 Feb 2012

...

....

13 Dec 2012

14 Jan 2013

....

Link this table to your fact and now you can use MonthID

Re: Need help on set analysis

v_iyyappan — Wed, 04 Apr 2012 16:39:08 GMT

Hi,

Use the below expression in Previous Month

=Count({<Year=,

Month=,

Datefield={'>=$(=(Num(Monthstart(AddMonths((Datefield),-1)))))<=$(=(Num(AddMonths((Datefield),-1))))'}>}

cost center Id)

Regards,

Iyyappan

Re: Need help on set analysis

Thu, 05 Apr 2012 02:51:58 GMT

Hi,

Let suppose I have one dimension (Super Department).. and my first expression is count(distinct cost center id] and how should I write my second expression so that it shows me the count(distinct [Cost Center Id]) for previous month..

Re: Need help on set analysis

Thu, 05 Apr 2012 04:28:26 GMT

Hi ,

Will this work if we dont have any selection on the date field?? Sorry this is not working...

Need help on set analysis

Jason_Michaelides — Thu, 05 Apr 2012 11:25:49 GMT

I think you'd better post your app so we can see more about what you need.