topic Re: Monthly value in QlikView

Monthly value

Thu, 31 Jul 2014 13:04:34 GMT

Hello everybody,

I am trying to do and P&L table showing:

- the values for the selected month if only one month is selected (=sum({$<Indicateur={"I3"} >} MONTANT)/1000)

- the monthly value of each month selected if several months are selected

- the YTD value (=sum({$<%Annee = {$(=only(%Annee))}, %NoMois = {"<=$(=max(%NoMois))"}, Indicateur={'I3'} >} MONTANT)/1000)

I know how to display the columns of the selected months but can't get to display the correct values. Everytime, the table shows the sum of all the months selected in each column.

Do you what expression I should type?

Thank you for your help,

Lorraine

Here are the expressions I tried:

=Sum({$<[%Nomois (#)] = {$(=Only([%NoMois (#)]))}, Indicateur={"I3"}>} MONTANT)

=Sum({$<[%Nomois (#)] = {$(=Only([%NoMois (#)]) – 1)}, Indicateur={"I3"}>} MONTANT)

=Sum({$<[Month (#)] = {$(=Max([Month (#)]) – 1)}, Indicateur={"I3"}>} MONTANT)

=sum({$< , Indicateur={"I3"}>} MONTANT)/1000

=sum({$<%Annee = {$(=only(%Annee))}, %NoMois = {$(=only(%NoMois))}, Indicateur={"I3"}>} MONTANT)

Re: Monthly value

JonnyPoole — Thu, 31 Jul 2014 13:25:53 GMT

The answer will depend on your data model.. can you share your QVW or a sample ?

Re: Monthly value

Thu, 31 Jul 2014 13:42:35 GMT

Here is the time script:

//Let vYear = Num(year(Today())) ;

set vToday = date(today(), 'DD/MM/YYYY');

LET vThisYear = year($(vToday)) ; //year(vToday);

LET vLastYear = year($(vToday)) - 1;

LET vThisQuarter = 'Q' & ceil(month($(vToday) / 3));

LET vThisMonth = num(month($(vToday)));

LET vThisMonthText = month($(vToday));

LET vLastMonth = num(month(addmonths($(vToday), -1)));

LET vLastMonthText = month(addmonths($(vToday), -1));

LET vTodayWithinYear = num($(vToday) - yearstart($(vToday))) + 1;

LET vTodayWithinMonth = num($(vToday) - monthstart($(vToday))) + 1;

Is this sample enough for you?

Re: Monthly value

JonnyPoole — Tue, 05 Aug 2014 14:57:27 GMT

Hi - just getting back to this. Can you share the QVW (scramble the data if needed) and show me which expressions or charts aren't giving you the right numbers ?