topic Dynamic changing as per selection in QlikView

Dynamic changing as per selection

vikasshana — Mon, 08 Oct 2018 13:54:10 GMT

Hi,

Looking for help in writing an expression. I’ve the below table in my qlik document.

Value	amount	date
A	100	01/01/2018
A	200	14/01/2018
A	300	03/02/2018
A	200	14/02/2018
A	200	06/03/2018
A	200	20/03/2018

‌

now my requirement is when every user selects month in list it has to show current month and previous month value. For example if user select January in month it has to display previous month values as 0 (because there is no previous month) and current month as ‘300’ as below.

Value	previous month	current month
A	Null or 0	300

IF the user selects February i want to see the output as below

Value	Previous month	current month
A	300	500

Message was edited by: Bharath Vikas Shanagonda I’m sorry I’ve updated my requirement. Instead of month column I will be using date column

Re: Dynamic changing as per selection

Anil_Babu_Samineni — Mon, 08 Oct 2018 14:14:28 GMT

May be this?

Where month_num created in script using below.

Num(Month(Date#(month,'MMMM'))) as month_num

Then, Create table

Previous Month

Sum({<month,month_num = {"$(=Max(month_num-1))"}>} amount)

Current Month

Sum({<month_num = {"$(=Max(month_num))"}>} amount)

Re: Dynamic changing as per selection

Anil_Babu_Samineni — Mon, 08 Oct 2018 14:51:24 GMT

PFA from Update?

Re: Dynamic changing as per selection

vikasshana — Mon, 08 Oct 2018 14:53:32 GMT

Just updated the requirement Anil.

Re: Dynamic changing as per selection

vikasshana — Mon, 08 Oct 2018 14:55:37 GMT

Didn’t have access to qlikview now as I’m using my mobile. Can you put the expression here please.

‌

Re: Dynamic changing as per selection

Anil_Babu_Samineni — Mon, 08 Oct 2018 14:58:28 GMT

Expression is same, Script looks this?

LOAD *, month(Date#(date,'DD/MM/YYYY')) as month,Num(month(Date#(date,'DD/MM/YYYY'))) as month_num Inline [

Value, amount, date

A, 100, 01/01/2018

A, 200, 14/01/2018

A, 300, 03/02/2018

A, 200, 14/02/2018

A, 200, 06/03/2018

A, 200, 20/03/2018

];

Re: Dynamic changing as per selection

vikasshana — Mon, 08 Oct 2018 16:51:24 GMT

thanks for that Anil, it is working fine and i've tested in straight table. Based on the selection it is giving me the result which I'm expecting. I'm giving the label name for an expression manually (current and previous) but I'm looking for an expression to change the label name dynamically.

For example if I select Feb month in the list box instead of previous the label name has to be Jan and for current it has to be Feb.

Re: Dynamic changing as per selection

vikasshana — Tue, 09 Oct 2018 07:02:29 GMT

stalwar1‌ can you help me on this please.

Re: Dynamic changing as per selection

Anil_Babu_Samineni — Thu, 11 Oct 2018 00:28:30 GMT

You can use Label like

Prev Month

=Date(Max(month)-1,'MMMM')

Current Month

=Date(Max(month),'MMMM')