Solved: Expression - Qlik Community

Report Inappropriate Content · ‎2011-08-04

Dear All,

I Want to diaplsy the Closing Value of an item bases on the user input AS OF DATE

For Example User Entered AS OF DATE as 27-APR-2011

Then In My Pivot Table it should give closing values as

ITEM_CODE	Closing Value
TTEF2001	130,092.26
TTEF2004	6,858.08

Closing Value = NEW_QUANTITY * ACUTAL_COST ( The Condition is transaction date should be <= as of date and transaction_id is max )

MY Table Data is pasted below

ITEM_CODE	TRANSACTION_DATE	TRANSACTION_ID	ACTUAL_COST	NEW_QUANTITY
TTEF2001	31-Mar-2011	3343353	18.24	1292
TTEF2001	31-Mar-2011	3345529	18.24	1532
TTEF2001	11-Apr-2011	3713122	18.24	1531
TTEF2001	11-Apr-2011	3715422	18.20	8731
TTEF2001	15-Apr-2011	3773876	18.21	8635
TTEF2001	18-Apr-2011	3818303	18.21	8395
TTEF2001	19-Apr-2011	3847769	18.21	8393
TTEF2001	21-Apr-2011	3881203	18.21	7625
TTEF2001	27-Apr-2011	3972880	18.21	7440
TTEF2001	27-Apr-2011	3972881	18.21	7385
TTEF2001	27-Apr-2011	3972882	18.21	7145
TTEF2001	28-Apr-2011	4009944	18.21	6617
TTEF2001	28-Apr-2011	4013965	18.21	6137
TTEF2001	29-Apr-2011	4042139	18.20	10169
TTEF2001	29-Apr-2011	4044546	18.20	9689
TTEF2001	29-Apr-2011	4045806	18.20	8729
TTEF2001	29-Apr-2011	4047560	18.20	8249
TTEF2001	29-Apr-2011	4048444	18.20	7049
TTEF2001	30-Apr-2011	4059263	18.20	7001
TTEF2001	30-Apr-2011	4080851	18.20	6761
TTEF2001	30-Apr-2011	4082582	18.20	6377
TTEF2001	04-May-2011	4152570	18.20	5897
TTEF2001	05-May-2011	4161593	18.24	6473
TTEF2001	06-May-2011	4172393	18.21	6281
TTEF2001	09-May-2011	4279156	18.21	5321
TTEF2001	09-May-2011	4279311	18.21	4608
TTEF2001	09-May-2011	4279312	18.21	4361
TTEF2001	13-May-2011	4432702	18.21	4217
TTEF2001	20-May-2011	4529102	18.21	3497
TTEF2001	24-May-2011	4583619	18.20	5465
TTEF2001	25-May-2011	4602800	18.20	4985
TTEF2001	25-May-2011	4603677	18.20	4889
TTEF2001	25-May-2011	4606494	18.20	4505
TTEF2001	26-May-2011	4622333	18.20	8537
TTEF2001	28-May-2011	4664510	18.20	8057
TTEF2001	28-May-2011	4665313	19.00	12089
TTEF2001	30-May-2011	4702985	19.00	13625
TTEF2001	31-May-2011	4749462	18.53	13145
TTEF2001	31-May-2011	4757639	18.53	12425
TTEF2001	31-May-2011	4758658	18.53	12144
TTEF2001	31-May-2011	4758659	18.53	12089
TTEF2001	31-May-2011	4762545	18.53	12041
TTEF2001	31-May-2011	4773505	18.53	11568
TTEF2001	31-May-2011	4773506	18.53	11561
TTEF2001	31-May-2011	4775063	18.53	10601
TTEF2004	31-Mar-2011	3343677	17.02	405
TTEF2004	19-Apr-2011	3847770	17.02	403
TTEF2004	28-Apr-2011	4009765	16.80	5155
TTEF2004	28-Apr-2011	4009945	16.82	4752
TTEF2004	28-Apr-2011	4009946	16.82	4627
TTEF2004	28-Apr-2011	4013966	16.82	3955
TTEF2004	29-Apr-2011	4044547	16.82	3235
TTEF2004	29-Apr-2011	4045807	16.82	2035
TTEF2004	29-Apr-2011	4047016	16.82	1075
TTEF2004	29-Apr-2011	4047561	16.82	595
TTEF2004	29-Apr-2011	4049764	16.82	355
TTEF2004	30-Apr-2011	4059264	16.82	259
TTEF2004	13-May-2011	4432703	16.82	115
TTEF2004	31-May-2011	4746642	19.00	4915
TTEF2004	31-May-2011	4747300	19.00	9715
TTEF2004	31-May-2011	4749463	18.97	9600
TTEF2004	31-May-2011	4749464	18.97	8035
TTEF2004	31-May-2011	4757640	18.97	7315
TTEF2004	31-May-2011	4758660	18.97	6355
TTEF2004	31-May-2011	4760007	18.97	5635
TTEF2004	31-May-2011	4762546	18.97	5587
TTEF2004	31-May-2011	4773507	18.97	5011
TTEF2004	31-May-2011	4774051	18.97	4800
TTEF2004	31-May-2011	4774052	18.97	4771
TTEF2004	31-May-2011	4775064	18.97	3811

Miguel_Angel_Baeyens · ‎2011-08-05

Hi,

Create a new pivot table chart with ITEM_CODE as dimensino and the following as expression:

Sum(If(Aggr(NODISTINCT Max(TRANSACTION_ID), TRANSACTION_DATE, ITEM_CODE) = TRANSACTION_ID AND Aggr(NODISTINCT Max({< TRANSACTION_DATE = {'<=$(=Date(vDate))'} >} TRANSACTION_DATE), ITEM_CODE) = TRANSACTION_DATE, NEW_QUANTITY * ACTUAL_COST))

Kind of cumbersome and take care of the performance should you have a large data volume, but the above should work.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

View solution in original post

Miguel_Angel_Baeyens · ‎2011-08-05

Hi,

Create a new pivot table chart with ITEM_CODE as dimensino and the following as expression:

Sum(If(Aggr(NODISTINCT Max(TRANSACTION_ID), TRANSACTION_DATE, ITEM_CODE) = TRANSACTION_ID AND Aggr(NODISTINCT Max({< TRANSACTION_DATE = {'<=$(=Date(vDate))'} >} TRANSACTION_DATE), ITEM_CODE) = TRANSACTION_DATE, NEW_QUANTITY * ACTUAL_COST))

Kind of cumbersome and take care of the performance should you have a large data volume, but the above should work.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Report Inappropriate Content · ‎2011-08-05

Thank you very much its working find with little modification.

From: Miguel Angel Baeyens

Sent: Friday, August 05, 2011 2:11 PM

To: Chandrashekar Jakkula

Subject: - Re: Expression

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Expression

created by Miguel Angel Baeyens<http://community.qlik.com/people/mabaeyens> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/138679#138679

Expression

Related Topics