Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help on set analysis

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

13 Replies
Jason_Michaelides
Partner - Master II
Partner - Master II

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

Not applicable
Author

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
Partner - Master II
Partner - Master II

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

Not applicable
Author

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

Not applicable
Author

Hi,

Do anyone have any suggestions .....

Jason_Michaelides
Partner - Master II
Partner - Master II

Maybe look at Above()

Not applicable
Author

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

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

v_iyyappan
Specialist
Specialist

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

Not applicable
Author

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