topic Re: Dynamic Change Value according rolling dates with user selection in QlikView

Dynamic Change Value according rolling dates with user selection

rakeshkumar1890 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

Need your help to find out the solution of my query.

Scenario is based on date by user selection, Sample file attached

1. As you see on sample file, on orderno 100, started Availabledate is "2019-12-16" which should be always output "Same". If user selected between the dates hence it should be also be output "same", because at that time the Availabledate would be started date.

2. if the Availbaledate is greater than previous availabledate then output should be "A"

3. if the Availbaledate is Less than previous availabledate then output should be "B" else (Currentavailable date is equal to previousdate then) "Same"

Kind Regards

Re: Dynamic Change Value according rolling dates with user selection

Taoufiq_Zarra — Mon, 06 Jul 2020 11:21:24 GMT

Hi @rakeshkumar1890

for the example below

and the script :

Data: load *, if(OrderNo<>peek(OrderNo) or Date#(AvailableDate,'YYYY-MM-DD')=previous(Date#(AvailableDate,'YYYY-MM-DD')),'Same',if(Date#(AvailableDate,'YYYY-MM-DD')>previous(Date#(AvailableDate,'YYYY-MM-DD')),'A','B')) as output inline [ OrderNo,PlanDate,Seq,AvailableDate 100,2019-12-16,1,2019-12-16 100,2019-12-17,2,2019-12-18 100,2019-12-19,4,2019-12-31 100,2019-12-17,5,2019-12-31 200,2019-12-17,1,2019-01-16 200,2019-12-30,2,2019-12-31 300,2020-01-07,1,2020-01-01 300,2020-01-07,2,2020-01-30 300,2020-01-07,3,2020-01-10 ];

I can get the output :

Re: Dynamic Change Value according rolling dates with user selection

rakeshkumar1890 — Tue, 07 Jul 2020 05:21:37 GMT

Thanks Taoufiq for the quick response.

But here is one problem, if I did this on script side it would not be dynamic,

suppose user selected Availabledate "2019-12-18" for OrderNo "100", then in this case output should be "Same" not "A", because it is the starting date.

Can we do something from expression side?

Kind Regards

Re: Dynamic Change Value according rolling dates with user selection

Taoufiq_Zarra — Tue, 07 Jul 2020 09:03:12 GMT

@rakeshkumar1890

Another option in the interface :

Dimension:

OrderNo,PlanDate,Seq, AvailableDate

Measure:

if(OrderNo<>above(total OrderNo) or AvailableDate=above(total AvailableDate),'Same', if(AvailableDate> above(total AvailableDate),'A','B'))

I left the old and the new output to see the difference.

Selected Date

attached qlikview file

Re: Dynamic Change Value according rolling dates with user selection

rakeshkumar1890 — Wed, 15 Jul 2020 11:35:31 GMT

Thanks Taoufiq