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]

What is the issue with what loveisfail‌ suggested here?

Dynamic changing as per selection

[vikasshana]

‌wwhen ever I select Jan in list box it is giving me ‘0’ in previous month

[sunny_talwar]

Try this script

Table:

LOAD *,

Date(MonthStart(date), 'MMM-YYYY') as MonthYear;

LOAD * INLINE [

    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

];

And now your expressions will be

Previous Month

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

Label for Previous Month

=Date(MonthStart(MonthYear, -1), 'MMM-YYYY')

Current Month

=Sum(amount)

Label for Current Month

=Max(MonthYear)

[vikasshana]

I've made changes to the script and to the expression, when ever i select month from list is giving me current value but not the previous value. Please check the below image.

[sunny_talwar]

Since you are making selection in Month field (instead of MonthYear)... try this

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

[sunny_talwar]

And probably ignore selection in yearto also

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

[vikasshana]

Thanks for that Sunny, it is working now. One last thing.

I'm using below expressions to get current, current-1 and current-2 data.

current:

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

current-1:

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

current-2:

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

but it is showing '0' in text box.

based on the selection only the values getting displayed.

Now my requirement is I want the values in textboxes for the respective months automatically with out selection in list box.

[sunny_talwar]

Try these

current:

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

current-1:

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

current-2:

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

Also, please spend some time reviewing set analysis

A Primer on Set Analysis

Dates in Set Analysis

[vikasshana]

Thanks for that Sunny, i will check the links. Coming to the above expression it is only working for current, still

current-1 and current-2 is showing 0