topic Re: Max value per Cyclic Group in QlikView

Max value per Cyclic Group

jjordaan — Fri, 15 May 2015 15:31:23 GMT

Hi QlikView addicts,

I want to show the stock figures in a line chart.

My dimension is a cyclic group with some calendar dimeniosn like, Year, Quarter, Month, Week, Date.

Within the ERP system everyday it will count the current stock per item and stores the current stock per item in a different table.

I'm using that table in QlikView.

I cannot sum the values because the total stock per item is per day.

So what I thought to do is to retrieve the max value per shown dimension from the Cyclic Group.

Can anybody help me how to retrieve the max value per dimension in my Cyclic Group (Year, Quarter, Month, Week, Date)

I thought it was easy and tried the below expression, but apparently for me it isn't that easy.

Aggr(Sum({<DateNo = {$(=Max(DateNo))}>} StockQuantity), [$(=GetCurrentField([Date-Dimensions]))]))

Thank you all for your help in advance

Re: Max value per Cyclic Group

Gysbert_Wassenaar — Fri, 15 May 2015 15:54:32 GMT

If possible try to add flag fields in the script for this. Something like:

LOAD

Date,

Item,

StockQuantity,

if(floor(Date)=floor(YearEnd(Date)),1,0) as IsYearEnd,

if(floor(Date)=floor(QuarterEnd(Date)),1,0) as IsQuarterEnd,

if(floor(Date)=floor(MontEnd(Date)),1,0) as IsMontEnd,

if(floor(Date)=floor(WeekEnd(Date)),1,0) as IsWeekEnd

FROM ....

You can create five simple expression like sum({<IsWeekEnd={1}>}StockQuantity). Use conditional expressions to show/hide these expressions as needed based on the current field from the cyclic group.

I realize the above may not be possible. For example if you don't have stock records for each day for the items. In that case perhaps you can use something like sum(aggr(firstsortedvalue(StockQuantity, -Date),StockItem, [$(=GetCurrentField([Date-Dimensions]))]))

Re: Max value per Cyclic Group

jjordaan — Fri, 15 May 2015 18:16:41 GMT

Hi Gysbert,

As always thank you for your help.

I'm not sure if it wil work for me, but I will test it and let you know

Re: Max value per Cyclic Group

ramoncova06 — Fri, 15 May 2015 19:26:05 GMT

you had a couple of extra brackets here in there, try with this one $(='['&GetCurrentField([Date-Dimensions])&']')

Re: Max value per Cyclic Group

jjordaan — Fri, 15 May 2015 19:55:45 GMT

Hi Ramon,

Thank you for your reply.

Unfortunately (for me) your suggestion didn't work.

Re: Max value per Cyclic Group

ramoncova06 — Fri, 15 May 2015 20:10:45 GMT

what values do you get when you add the expression I have you to listbox ?

Re: Max value per Cyclic Group

jjordaan — Thu, 28 May 2015 07:59:53 GMT

Hi,

Sorry for my late reply. I was traveling and very busy.

When I re-read my post I saw that my question is a bit confusing.

Sorry for that.

What I'm searching for is that if the Cyclic Group is showing Year-week I want the value for the last date of that week (per visible year-week).

If the Cyclic Group is showing Year-month I want the value for the last date of that month (per visible year-month).

Thanks again for your help

Re: Max value per Cyclic Group

ramoncova06 — Thu, 28 May 2015 14:33:31 GMT

haven't tested this yet, but it seems as you want something like this

Sum({<DateNo p= ({<DateNo = {"$(=Max( total <GetCurrentField([Date-Dimensions])>DateNo))"}>})>} StockQuantity)

Re: Max value per Cyclic Group

ramoncova06 — Thu, 28 May 2015 16:01:01 GMT

yes tested it and is not working, you might to go with Gysbert approach