Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to show data for the last month/day if only year is selected


in my QV app i have a list of shares from the market, for every one of them i have a given number (Qnt) that represent how many stock i bought and these data:





i've stored also other 2 value, first_date and last_date that represent the initial investment date and the date that i assume as "today", so..if i have to show the initial portfoglio value or the present one i've no problem and here comes the problems...if i select only a year from a listbox i want to show the value of the portfolio at the end of that year (ie 2005-12-31) or, if i select a year and a month i want to show the value of the portfolio at the end of that month.

i've tried anything but nothing seems to work.. the if statement are not an issue the problem is setting the value

this is my last try (i'm new to qlik and i don't know how to use properly set analysis).

I've set some variable to help me with if statement:

vOnlyYear  as =if(GetSelectedCount(Year)>0,True(),False())

vYearMonth as =if(GetSelectedCount(Year)+GetSelectedCount(Month)>=2,True(),False())

=if( vOnlyYear,Sum({$ <Month={12},Day={"$(max({$}Day))"}>}Qnt*Stock_price), )   the result is 0 no matter what i change 😕

for example: in 2009-02-27 the total value should be 438.908,54 but it shows 0


Message was edited by: Matteo Porcino

3 Replies
Not applicable

can u please attached your QV

Not applicable

here it is

Not applicable

You can use MonthEnd to get the last day of any month.

If you have a date field you can just use max(Date) instead so

sum({<Date = {$(=max(Date)}>}Qnt*Stock_price).

If you need to create a synthetic date field, you could try something like:

sum({<Month = {$(=Max(Month))},Day = {$(=Day(MonthEnd(Date('01/'&Max(Month)&'/'&Max(Year)))))}>}Qnt*Stock_price).

Haven't tested it but it should be fine

By way of explanation, all you are doing is creating a date that is set as the first day of the month and year that you are interested in and then finding the last day of that month.