Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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:
Stock:
Title,
Data,
Stock_price
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
Thanks!
Message was edited by: Matteo Porcino
can u please attached your QV
here it is
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.