topic To fetch Previous month data in QlikView

To fetch Previous month data

Tue, 05 Oct 2010 07:45:52 GMT

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

To fetch Previous month data

syed_muzammil — Tue, 05 Oct 2010 22:19:14 GMT

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.

To fetch Previous month data

Tue, 05 Oct 2010 22:47:33 GMT

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

To fetch Previous month data

syed_muzammil — Tue, 05 Oct 2010 22:49:13 GMT

Hi,

Look at this answer by John.

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

To fetch Previous month data

Wed, 06 Oct 2010 07:59:30 GMT

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

To fetch Previous month data

Wed, 06 Oct 2010 22:43:11 GMT

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

To fetch Previous month data

Thu, 07 Oct 2010 05:07:27 GMT

Hi,

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

To fetch Previous month data

Thu, 07 Oct 2010 05:48:22 GMT

Hi btechrames,

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

Hope this helps.

Rod

To fetch Previous month data

Thu, 07 Oct 2010 06:22:42 GMT

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

To fetch Previous month data

Thu, 07 Oct 2010 06:39:01 GMT

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

To fetch Previous month data

rwunderlich — Thu, 07 Oct 2010 06:49:50 GMT


Rod Jager wrote:=FIELDVALUE('PeriodName', FIELDINDEX('PeriodName', ONLY(PeriodName))-1)

Rod,

This technique requires that the periods were loaded in perfect ascending order, correct?

-Rob

To fetch Previous month data

Thu, 07 Oct 2010 06:57:45 GMT

Hi Rob,

Yes, good point, the data needs to be in the correct order - isn't everyones 😉 ?

Rod

To fetch Previous month data

Thu, 07 Oct 2010 07:29:19 GMT

Hi,

adding multiple expression its showing error in expression.Pls correct with the below condition.

if i select field values in PeriodName...it should display the sum of values related to that field.

if i select field values in SemName..it should display the sum of values related to that field.

To fetch Previous month data

Thu, 07 Oct 2010 22:37:14 GMT

Hi btechrames,

If you want the calculation to be conditional then consider using the GetSelectedCount function or similar to evaluate if any elements have been selected from either the PeriodName or SemName fields and then apply logic accordingly.

As an (untested) example:

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

Not very elegant but I hope this helps.

Rod