Need help in Qlikview expression

[vikasshana]

Hi,

I've below table in my qlikview and the script.

Script:

---------------

LOAD Value,

     amount,

     date,

     month(date) as monthto,

     Num(month(date)) as month_num,

     year(date) as yearto

FROM

(biff, embedded labels, table is Sheet1$);

Table:

-------------- 

amount	date	Value	month_num	monthto	yearto
200	13/12/2017	A	12	Dec	2017
450	20/12/2017	A	12	Dec	2017
100	01/01/2018	A	1	Jan	2018
200	14/01/2018	A	1	Jan	2018
300	03/02/2018	A	2	Feb	2018
100	15/02/2018	A	2	Feb	2018
200	02/03/2018	A	3	Mar	2018
150	20/03/2018	A	3	Mar	2018

I'm looking for expression in straight table qlikview when ever user selects Jan as month in list box I would like to see output as below

Value	Previous Month sum of sales	Current Month sum of sales
A	650	300

when ever user selects Feb as month I would like to see output as below.

Value	Previous Month sum of sales	Current Month sum of sales
A	300	400

and more over I'm giving expression name manually but i would like to change that to month name as well. For example if user selects Feb as month in list box instead of Current Month Sum of sales i want month name as Feb and instead of Previous Month sum of sales I want Month name as Jan.

Message was edited by: Bharath Vikas Shanagonda Straight table

[sunny_talwar]

Can you provide the file you are working with?

[vikasshana]

I'm using personal edition, please find the attached. ! !

[sunny_talwar · Accepted solution]

Misplaced my parenthesis... try this

=Sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}, monthto>}amount)

=Sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"}, monthto>}amount)

[vikasshana]

Yeah now it works perfect. Thanks for your help Sunny, much Appreciated..!!!!