[ASK] Create dynamic stock aging as selection

rendiyan — Sat, 03 Dec 2016 18:18:14 GMT

Dear Qlik Master,

I have a requeirement to create stock aging that able to change dynamically, depend on date selection.

And that aging have to able to show as a list box for filter selection.

Currently i able to create this dynamic aging as an expression in a chart, but i can't take it out and make it as a list box selection.

So my chart to create dynamic aging is below :

Date, max date, aging, and aging group are expression.

Date column actually only a transaction date column for each stock transaction (in-out) from transaction table.

Max Date is an expression that show max date based on calendar dimension from dimension table..

Aging is an expression based on [Date]-[Max Date].

And Aging Group is a grouping based on [Aging]

and for expression script :

Expression	Expression Script
Date	aggr( only( {< Year={'$(=max(Year))'}, MonthDay={'<=$(=max( {<Year={'$(=max(Year))'}>} MonthDay))'}, Quarter=,Month=,Week=,Day= >} [MS Date] ) ,[MS Date],[Code Region],[Code Brance],[Wh ID],ITEMNO,[Wh Name],[Wh Type] )
Max Date	max(total<Year,Quarter,Month> {1< Year=P(Year),Quarter=P(Quarter),QuarterNum=P(QuarterNum), Month=P(Month),MonthNum=P(MonthNum), Day=P(Day),DayNum=P(DayNum) >} DATE_DIM)
Aging	[Date] - [Max Date]

Expression

Expression Script

Date

aggr(

only(

Year={'$(=max(Year))'},

MonthDay={'<=$(=max( {<Year={'$(=max(Year))'}>} MonthDay))'},

Quarter=,Month=,Week=,Day=

[MS Date]

)

,[MS Date],[Code Region],[Code Brance],[Wh ID],ITEMNO,[Wh Name],[Wh Type]

)

Max Date

max(total<Year,Quarter,Month>

{1<

Year=P(Year),Quarter=P(Quarter),QuarterNum=P(QuarterNum),

Month=P(Month),MonthNum=P(MonthNum),

Day=P(Day),DayNum=P(DayNum)

DATE_DIM)

Aging

[Date] - [Max Date]

As additional info, my date selection dimension columns are : Year, Quarter, Month, Day..

And my question is,

is it possible to create dynamic aging as a list box?

If it is yes, is there any way and solution for this?

I've searched it and didn't found.

Many thx,

Best Regards,

Re: [ASK] Create dynamic stock aging as selection

rendiyan — Sun, 04 Dec 2016 06:13:28 GMT

is there no solution for this?

Re: [ASK] Create dynamic stock aging as selection

lironbaram — Sun, 04 Dec 2016 06:23:45 GMT

you can try something like this

put the below expression in a list box

aggr(

only(

Year={'$(=max(Year))'},

MonthDay={'<=$(=max( {<Year={'$(=max(Year))'}>} MonthDay))'},

Quarter=,Month=,Week=,Day=

[MS Date]

max(total<Year,Quarter,Month>

{1<

Year=P(Year),Quarter=P(Quarter),QuarterNum=P(QuarterNum),

Month=P(Month),MonthNum=P(MonthNum),

Day=P(Day),DayNum=P(DayNum)

DATE_DIM)

,[MS Date],[Code Region],[Code Brance],[Wh ID],ITEMNO,[Wh Name],[Wh Type]

)

topic Re: [ASK] Create dynamic stock aging as selection in QlikView

[ASK] Create dynamic stock aging as selection

Re: [ASK] Create dynamic stock aging as selection

Re: [ASK] Create dynamic stock aging as selection