Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator III
Creator III

Need help in Qlikview expression

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:

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

amountdateValuemonth_nummonthtoyearto
20013/12/2017A12Dec2017
45020/12/2017A12Dec2017
10001/01/2018A1Jan2018
20014/01/2018A1Jan2018
30003/02/2018A2Feb2018
10015/02/2018A2Feb2018
20002/03/2018A3Mar2018
15020/03/2018A3Mar2018

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

ValuePrevious Month sum of salesCurrent Month sum of sales
A650300

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

ValuePrevious Month sum of salesCurrent Month sum of sales
A300400

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

13 Replies
sunny_talwar

What is the issue with what loveisfail‌ suggested here?

Dynamic changing as per selection

vikasshana
Creator III
Creator III
Author

‌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
Creator III
Creator III
Author

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.

Qlikview.png

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
Creator III
Creator III
Author

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.

2.png

based on the selection only the values getting displayed.

1.png

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
Creator III
Creator III
Author

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