topic Re: current week on aggr formula in QlikView

current week on aggr formula

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello there!

Can someone help me modify my formula in such a way that it can reflect the current week that I need.

below is my formula

=Sum(Aggr(QTY,LOTS))

I have tried this but it is giving me a zero (0) result

Sum({<Fiscal_Week = {"$(=MaxString(Fiscal_Week))"}>}Aggr(QTY,LOTS))

thanks

Re: current week on aggr formula

dinuwanbr — Mon, 02 Oct 2017 07:24:57 GMT

Try this

=Sum( {<Fiscal_Week = {"$(=Max(Fiscal_Week))"}>} Aggr( QTY,LOTS))

if it is possible, Can you upload a sample here?

Rgds,

Tharindu

Re: current week on aggr formula

devarasu07 — Mon, 02 Oct 2017 07:25:10 GMT

Hi,

Try like this

=aggr(Sum({$<Fiscal_Week ={$(=max(Fiscal_Week))}>} QTY),LOTS)

Re: current week on aggr formula

its_anandrjs — Mon, 02 Oct 2017 07:26:47 GMT

That is correct expression those you using you can try with Max Also

=Sum( {<Fiscal_Week = {"$(=MaxString(Fiscal_Week))"}>} Aggr( QTY,LOTS))

=Sum( {<Fiscal_Week = {"$(=Max(Fiscal_Week))"}>} Aggr( QTY,LOTS))

See my attached also on which added field Fiscal_Week to check change.

Re: current week on aggr formula

Anonymous — Mon, 02 Oct 2017 08:13:29 GMT

Hi,

I have tried all your suggestions but still giving me zero output.

i'll try to check my data if there is something work with my fiscal week.

if there are other suggestions i am still waiting for the right formula.

thanks

Re: current week on aggr formula

its_anandrjs — Mon, 02 Oct 2017 08:16:23 GMT

Just check fiscal week is a numeric field or string field in your data model.

Re: current week on aggr formula

antoniotiman — Mon, 02 Oct 2017 08:19:35 GMT

May be this

Sum(Aggr(Sum({$<Fiscal_Week ={$(=Max(Fiscal_Week))}>} QTY),LOTS))

Re: current week on aggr formula

its_anandrjs — Mon, 02 Oct 2017 08:23:18 GMT

Or maybe this but not sure check at your end

Sum(Aggr(Sum(DISTINCT {$<Fiscal_Week ={$(=Max(Fiscal_Week))}>} QTY),LOTS))

Re: current week on aggr formula

Anonymous — Mon, 02 Oct 2017 08:38:06 GMT

Hi Anand and all,

I have checked my data and it seems it is giving me the max week in my record which has a zero value that is why i am getting the zero result.

to make use of Anand's sample file

week 13 is my current week

week 16 is the max week which has no value yet

in the formula it is giving me the week 16 zero value. what I am after is the week 13 (current week)

hope this helps...

Re: current week on aggr formula

Anonymous — Fri, 06 Oct 2017 05:06:49 GMT

Hi All,

I would like to make a follow up on my query and hope someone can help me.

I have made some revisions to better understand my dilemma.

in the sample file I have added 3 weeks(week 14,15,16)

in the formula below is giving me zero value since it is pointing me to week 16

Sum(Aggr(Sum(DISTINCT {$<Fiscal_Week ={$(=Max(Fiscal_Week))}>} QTY),LOTS))

the result i am after is to get the week 13 data.

thanks and hoping for a positive result.

Re: current week on aggr formula

yasmeenk — Fri, 06 Oct 2017 05:36:35 GMT

Hi,

Please check the attached file.

Is this what you are trying to get?

Re: current week on aggr formula

its_anandrjs — Fri, 06 Oct 2017 05:46:17 GMT

What output you need means for which row and week data in output.

Re: current week on aggr formula

dinuwanbr — Fri, 06 Oct 2017 06:16:21 GMT

Hi,

PFA

Rgds,

Tharindu

Re: current week on aggr formula

Anonymous — Fri, 06 Oct 2017 07:26:18 GMT

Hi Yasmeen,

You nailed it right! this is what I am looking for...

thank you so much!

Re: current week on aggr formula

yasmeenk — Fri, 06 Oct 2017 08:24:12 GMT

You're welcome

Re: current week on aggr formula

Anonymous — Sat, 07 Oct 2017 06:07:36 GMT

Hi Yas,

follow up question please. I wanted to get the percentage of lots based on hours criteria.

how do i modify this formula in order to get the percent per hour.

=Count( {<Fiscal_Week = {$(=Max({<CRITERIA={"<6 HRS"}>}Fiscal_Week))}>} LotID)/Count({<Fiscal_Week = {"$(=MaxString(Fiscal_Week))"}>}LotID)

base on this data(in pivot expression)

CRITERIA <6 HRS 6-12 HRS >24 HRS OTHERS TOTAL

# OF LOTS 346 535 290 375 1546

PERCENT 22% 35% 19% 24% 100%

where % is #OF LOTS over TOTAL

thanks in advance

Re: current week on aggr formula

Anonymous — Sat, 07 Oct 2017 08:35:02 GMT

Hi Yas and all,

found it! a stroke of luck for me

this formula gives me the right answer I'm looking for. The TOTAL is the key.

=Count( {<Fiscal_Week = {$(=Max({<CurMainQty={">0"}>}Fiscal_Week))}>} LotID) / Count(TOTAL {<Fiscal_Week = {$(=Max({<CurMainQty={">0"}>}Fiscal_Week))}>} LotID)