Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

current week on aggr formula

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

1 Solution

Accepted Solutions
yasmeenk
Partner - Creator
Partner - Creator

Hi,

Please check the attached file.

Is this what you are trying to get?

View solution in original post

16 Replies
dinuwanbr
Creator III
Creator III

Try this

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

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


Rgds,

Tharindu


devarasu07
Master II
Master II

Hi,

Try like this

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

its_anandrjs

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.



Anonymous
Not applicable
Author

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

its_anandrjs

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

antoniotiman
Master III
Master III

May be this

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

its_anandrjs

Or maybe this but not sure check at your end

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

Anonymous
Not applicable
Author

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...

Anonymous
Not applicable
Author

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.