topic Re: Display last value in range instead of total in QlikView

Display last value in range instead of total

Thu, 21 Jul 2016 19:33:19 GMT

Hi All,

I have a pivot table that represents number of donors by segment and month (see the attachment).

On this pivot table for Active donors in total I want to display the last Month Value instead of total.

Is it possible? Please Help.

Thanks

Re: Display last value in range instead of total

settu_periasamy — Thu, 21 Jul 2016 23:50:36 GMT

Try with dimensionality function. . Like

If (dimensionality ()=1 and donorsfield='Active',

Sum ({<Month={"=$(max (month))"}>}value), your general expression )

Re: Display last value in range instead of total

michael_anthony — Thu, 21 Jul 2016 23:57:35 GMT

Hi Denis,

I assume three fields, Month, Type and Data (the value).

The expression to do it had two parts:

a) differentiate on whether it is a normal or total value.

I had two options:

If(SecondaryDimensionality()=1, Month Calc, Total Calc )

If(Count(Distinct Month)=1, Month Calc, Total Calc ).

Either works, but secondarydimensionality() may be an issue if pivot to put Month on the vertical axis instead of horizontal.

Month Calc was simply Sum(Data).

Total Calc I had two methods after playing around:

a) Sum(If(Month = $(=Max(Month)),Data)).

This calculates Max(Month) based on selections at the document level and then only includes the Month based on that, which will always be the last month.

b) Sum(Aggr(If(Only(Month) = Max(Total <Type> Month),Sum(Data)),Month,Type)).

Similar method but checks for the Max month within the chart, rather than the document.

Hopefully this helps.

Re: Display last value in range instead of total

Fri, 22 Jul 2016 13:08:25 GMT

Hi Settu,

Thanks for replay,

It still does not display lust Month data in a total.

Re: Display last value in range instead of total

Fri, 22 Jul 2016 13:13:53 GMT

Hi Michael,

Thanks for replay.

I am afraid that I do not clearly understand your formula.

Currently I have one dimension - Month in my pivot table and the Active donor expression (see below)

Count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'Active'}>} Distinct [Constituent ID]).

Could you kindly explain how I can insert my formula into yours?

Thanks

Re: Display last value in range instead of total

settu_periasamy — Fri, 22 Jul 2016 19:46:05 GMT

Check the Attachment

Re: Display last value in range instead of total

Fri, 22 Jul 2016 19:51:29 GMT

Hi Settu,

Thank you for replay.

Unfortunately I am using personal version of Qlikview as my company just planning to launch it, so I cannot open your file.

Re: Display last value in range instead of total

settu_periasamy — Fri, 22 Jul 2016 19:57:10 GMT

That is fine. in the pivot table try this expression

=if(Dimensionality()=1 and SecondaryDimensionality()=0 and Type='Active',sum({<Month={"$(=MonthName(Max(Month)))"}>}Data),sum(Data))

I assumed, month field would be like Apr 2016, May 2016... Mar 2015

Re: Display last value in range instead of total

michael_anthony — Sun, 24 Jul 2016 23:20:28 GMT

If(SecondaryDimensionality()=1,

Count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'Active'}>} Distinct [Constituent ID]),

Count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'Active'}, Month = {$(=Max(Month))}>} Distinct [Constituent ID] ) )

)

I'm not sure what your Month field is called, so substitute that in. I went with the SecondaryDimensionality() which identifies whether it's a data or subtotal column. Risky if switch Month to rows though, but can add the Dimensionality() function also to test for both.

Re: Display last value in range instead of total

Tue, 26 Jul 2016 12:45:37 GMT

Hi Settu,

I tried unfortunately it did not work.

My month field is Month like April, May,....

The month filed is the only dimension I am using all the others are calculated.

Below is the formula I am using to get the Active donors numbers:

Count({<FiscalYear = {'$(=maxstring(FiscalYear))'},[Gift Type] = {[Recurring Gift Pay-Cash]}, DonorCurentSegment = {'Active'}>} Distinct [Constituent ID]).

Please let me know if you have any ideas.

Thanks a lot for your help.

Re: Display last value in range instead of total

Tue, 26 Jul 2016 12:54:59 GMT

Hi Michael,

Thanks for your help.

Unfortunately the expression gives me zeros when I use it.

However when I change SecondaryDimensionality to Dimensionality it gives me numbers under Months but nothing in total

Do you have any ideas why.

Thanks a lot for your help.

Denis

Re: Display last value in range instead of total

Fri, 29 Jul 2016 12:59:53 GMT

Hi all.

Is there anybody who can help me with this issue?

Any help would be much appreciated.

Thanks.

Denis

Re: Display last value in range instead of total

Anonymous — Fri, 29 Jul 2016 13:21:04 GMT

please read the following , I am not sure whether it relates to or not ..

thanks to gwassenaar

Display most recent record in text box

https://community.qlik.com/message/1086065#1086065

Re: Display last value in range instead of total

settu_periasamy — Fri, 29 Jul 2016 14:08:46 GMT

Hi Denis,

Can you post the sample qvw instead of excel?

Re: Display last value in range instead of total

Fri, 29 Jul 2016 14:47:15 GMT

Hi Settu,

Can you kindly let me know how to attach qvw file?

The only attachment options I could find are image, video or link.

Thanks

Re: Display last value in range instead of total

settu_periasamy — Fri, 29 Jul 2016 15:34:09 GMT

When you do the reply, in top right corner, there is an option 'Use advanced editor'.

After clicked that, you can see the attach option in the same window

Re: Display last value in range instead of total

Fri, 29 Jul 2016 15:52:29 GMT

I do not have that option on my screen?

Re: Display last value in range instead of total

Anil_Babu_Samineni — Fri, 29 Jul 2016 15:58:16 GMT

Instead of this, What are you expecting

Re: Display last value in range instead of total

Anil_Babu_Samineni — Fri, 29 Jul 2016 16:06:16 GMT

Please check that, In the Right Corner you can see. After that click and down right side you will get Attach Then click and then Upload your File

Re: Display last value in range instead of total

settu_periasamy — Fri, 29 Jul 2016 16:07:59 GMT

i think you are replying from your inbox. you need to click your link.

Display last value in range instead of total