topic Re: FirstSortedValue and total in a pivot in QlikView

FirstSortedValue and total in a pivot

p_andreev — Mon, 08 Dec 2014 08:15:36 GMT

Hi everybody,

Could someone help with the following issue:

I have this expression -

Rangesum(FirstSortedValue({$<Year=,Month=, Day=, ItemAndDayYearMonth={"=$(=Max({$<ItemAndDayYearMonth={'<=$(=Year(Max(Date))*100+Num(Month(Max(Date))))'}>} ItemAndDayYearMonth))"}>} ItemCostValue/Rate,-Date))

and I can't get a total on this column in a pivot. Is the problem in FirstSortedValue or Rangesum function ?

Re: FirstSortedValue and total in a pivot

tresB — Mon, 08 Dec 2014 08:22:14 GMT

Remove rangesum(), and try like:

FirstSortedValue({$<Year=,Month=, Day=, ItemAndDayYearMonth={"=$(=Max({$<ItemAndDayYearMonth={'<=$(=Year(Max(Date))*100+Num(Month(Max(Date))))'}>} ItemAndDayYearMonth))"}>} ItemCostValue/Rate,-Date)

Re: FirstSortedValue and total in a pivot

p_andreev — Mon, 08 Dec 2014 08:48:53 GMT

Yes, I have tried this and unfortunately it does not work.

Re: FirstSortedValue and total in a pivot

tresB — Mon, 08 Dec 2014 09:05:49 GMT

Can you share your sample qvw?

Re: FirstSortedValue and total in a pivot

p_andreev — Mon, 08 Dec 2014 11:58:34 GMT

tresesco, thank you for your willingness to help.

I found a solution including the above expression in Aggregation at the lowest level of the dimensions.

Have a nice day.