To fetch Previous month data

[#1]

Hi,

I need to calculate the sum of values of previous month data depends on the current month selection,,,

PeriodName field consist of jan,2008,feb200

Sum ({$<PeriodName={$(#PeriodN)}>}Values)

i used this expression with the help option to get previous year values,,,PeriodN is the variable but it shows the current selected month and also nt showing values ,

i assigned PeriodN=PeriodName in input box proper,,,

i need sum values to be calculated for previous month....pls help me out,,thanks in advance

[syed_muzammil]

Hi,

Try using num(Month) which gives u the number of the month selected and then put that in a variable. In the expression user that variable-1 to get values for previous month.

Regards,

Syed.

[#3 · Accepted solution]

Hi btechrames,

There are two steps to getting this to work.

First is to make sure your variable PerioN holds the previous months value by using a formula that does something similar to the following (note this variable will only work if a single value is selected in PeriodName):

=FIELDVALUE('PeriodName', FIELDINDEX('PeriodName', ONLY(PeriodName))-1)

Second is to go with the formula you have but make a minor change and that is to put quotes around the variable name as per the following:

Sum ({$<PeriodName={'$(#PeriodN)'}>}Values)

There are other ways of getting this to work but seeing you have already started down this path hopefully this will be of some help.

Rod

[syed_muzammil]

Hi,

Look at this answer by John.

http://community.qlik.com/forums/t/35015.aspx

[#5]

Thanks Rod Jager..its working..

Is any other possibility to bring the values using the functions fieldvalue,fieldindex in an expression statement itself to find the same sum of values.

without using variable and assigning it,,,

[#6]

Hi btechrames,

You can place those functions directly into the Sum expression as per the following example:

Sum ({$<PeriodName={'$(=FIELDVALUE('PeriodName', FIELDINDEX('PeriodName', ONLY(PeriodName))-1))'}>}Values)

Hope this helps.

Rod

[#7]

Hi,

its not working,,it shows error in expression..pls correct....help.

[#8]

Hi btechrames,

You need to select a value from the PeriodNames field to ensure the expression calculates.

Hope this helps.

Rod

[#9]

Hi Rod Jager,

its working when i select in periodname...

And also i need to work when i select in SemName field...it contains sem apr 2009,sem apr 2010 and so on.....and QTRName field...

How to add those field in an single expression...any possibility with if condition,,,

pls provide...

[#10]

Hi btechrames,

To use the logic for other fields, copy the formula and replace the PeriodName field name with your other field names.

If you want to add multiple expressions into a single calculation then try something like the following:

Sum ({$<SemName={'$(=FIELDVALUE('SemName', FIELDINDEX('SemName', ONLY(SemName))-1))'},PeriodName={'$(=FIELDVALUE('PeriodName', FIELDINDEX('PeriodName', ONLY(PeriodName))-1))'}>}Values)

Hope this helps.

Rod