Need help on set analysis

[#1]

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....

[Jason_Michaelides]

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

[#3]

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.

[Jason_Michaelides]

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

[#5]

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

[#6]

Hi,

Do anyone have any suggestions .....

[Jason_Michaelides]

Maybe look at Above()

[#8]

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

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

[v_iyyappan]

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

[#10]

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