topic Re: Filter by Max(Date) in Set Analysis in QlikView

Filter by Max(Date) in Set Analysis

Wed, 04 Feb 2015 20:01:39 GMT

Hi All!!

This it´s my first post

I have a textbox with the following expression=

if(getselectedcount (calend.month)=0,

sum({1<[DIM_MONEDA_ID] = {'USD'},common_date={'>=$(vMaxDate)'}, FIG.FACT_EST_TABLA_ORIGEN={'REAL'},FIG.DIM_ITEM_REPORTE_KEY={'CONTRIBUTION MARGIN'} >} FIG.FACT_EST_VALOR),

sum({1<[DIM_MONEDA_ID] = {'USD'},common_date=P(common_date),FIG.FACT_EST_TABLA_ORIGEN={'REAL'},FIG.DIM_ITEM_REPORTE_KEY={'CONTRIBUTION MARGIN'} >}

if(common_date=$(vMaxDate),FIG.FACT_EST_VALOR)))

where :

calend.month = Month on master calendar

DIM_MONEDA_ID = Currency dimension, i'm filtering only by USD

common_date = master date in master calendar

FIG.FACT_EST_TABLA_ORIGEN = Always "REAL"

FIG.DIM_ITEM_REPORTE_KEY = Always "CONTRIBUTION MARGIN"

FIG.FACT_EST_VALOR = Field to be added in expression

The calend.month are filter by user. Texbox will be show only the max month selected when currency = USD, Tabla Origen = "Real" and ITEM_REPORTE_KEY = "CONTRIBUTION MARGIN". If the user haven't made selections, textbox shows the value of the last month charged.

I have the following behavior associated to month selections by users

- If the user haven't month selected, textbox shows correctly the sum(values) of last month.

- If the users have one month selected, textbox shows correctly the sum(values) of selected month

- If the users have 2 or more month selected, textbox shows the sum of all month selected.

I need to shows always the max(month) independent of month selections.

Any suggestion?

PS: Please apologies for my english.

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Wed, 04 Feb 2015 20:23:15 GMT

For max(month) regardless of selection you can use Max ({1} month). This will ignor any selections you make in your application.

Update: If it should not change only based on a selection in month, then you can use Max({<month = >} month)

Best,

Re: Filter by Max(Date) in Set Analysis

Thu, 05 Feb 2015 13:20:09 GMT

Thanks Suninidia for your prompt answer!.

Please excuse me if I didn't explain well.

I want to calculate the sum() of value when currency = USD, Tabla Origen = "Real" and ITEM_REPORTE_KEY = "CONTRIBUTION MARGIN" for max(month). If the user have made selections(1 month or more),the sum should be the maximum month related to user selection.

Could you have any suggestion to solve this?.

Thanks again!.

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Thu, 05 Feb 2015 13:29:42 GMT

Sorry about not interpreting you question correctly the first time.

Is your calend.month field a date field (Jan-2014) or is it Text(Jan, Feb, Mar...) ?

Best,

Re: Filter by Max(Date) in Set Analysis

Thu, 05 Feb 2015 13:49:32 GMT

Hi Sunindia,

There is my calend table. calend.month is a text field. For this reasons I'm using "common_date" field to manage date filters.

calend.year	calend.month	calend.quarter	common_date
2014	jan	T1	01-01-2014
2014	feb	T1	02-01-2014
2014	mar	T1	03-01-2014
2014	apr	T2	04-01-2014
2014	may	T2	05-01-2014
2014	jun	T2	06-01-2014
2014	jul	T3	07-01-2014
2014	aug	T3	08-01-2014
2014	sep	T3	09-01-2014
2014	oct	T4	10-01-2014
2014	nov	T4	11-01-2014
2014	dec	T4	12-01-2014

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Thu, 05 Feb 2015 13:56:12 GMT

Update: you will need to do this in the script.

Is replacing calend.month to a date field a possibility?

If you use MonthName(common_date) as calend.month it will give you something like this Jan-2014, Feb-2014, Mar-2014... and so on.

This way you can use MaX(calend.month) directly in your set analysis.

Best,

Re: Filter by Max(Date) in Set Analysis

Thu, 05 Feb 2015 15:22:07 GMT

I'm applying your solution with no luck

I'm sharing a sample QVW file with data and formulas. Please review and fix it if possible.

Thanks!!

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Thu, 05 Feb 2015 15:53:41 GMT

See if this makes sense. PFA

Best,

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Thu, 05 Feb 2015 16:01:40 GMT

This one is even be better. PFA

Best,

Re: Filter by Max(Date) in Set Analysis

Thu, 05 Feb 2015 16:46:19 GMT

I'ts work perfectly. You're save my day!.

Thanks you.

Christian.

Re: Filter by Max(Date) in Set Analysis

sunny_talwar — Thu, 05 Feb 2015 16:52:28 GMT

I am glad that I proved useful.

Best,