topic Re: Need help in Qlikview expression in QlikView

Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 10:34:49 GMT

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

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 11:54:51 GMT

What is the issue with what loveisfail‌ suggested here?

Dynamic changing as per selection

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 11:57:33 GMT

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

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 12:04:41 GMT

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)

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 12:24:00 GMT

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.

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 12:28:10 GMT

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

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

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 12:28:44 GMT

And probably ignore selection in yearto also

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

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 13:01:24 GMT

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.

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 13:10:29 GMT

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

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 13:18:54 GMT

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

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 13:22:44 GMT

Can you provide the file you are working with?

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 13:29:42 GMT

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

Re: Need help in Qlikview expression

sunny_talwar — Tue, 09 Oct 2018 13:35:33 GMT

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)

Re: Need help in Qlikview expression

vikasshana — Tue, 09 Oct 2018 13:54:18 GMT

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