topic Re: get the max value after aggr.... in QlikView

get the max value after aggr....

RheaQv12 — Wed, 02 Oct 2019 02:57:22 GMT

Hi,

i have a table in which i have the transaction date like PostedDate - i derived the last 7 days flag in the load script..

in a straight table i am trying to calculate the last 7 days using the flag and the following expression..

i need to pick the max value for the combination of PostedDate and the name

=if(PDate_7_Days=1 ,aggr(Max(aggr(sum(SumCounter),[PDate],[Name])),Name,[PDate]) ,0)

in my straight table i have other columns along with Name, PDate but i need to aggr by Name, PDate and get the sum(SumCounter) for the last 7 days (this is a flag) - from the above if i want to get the max value of (highlighted in yellow)

can someone please help... for each PDate and each Name of the file i need to pick the MAX value so that i get the last 7 days numbers

any help..

Re: get the max value after aggr....

Anil_Babu_Samineni — Wed, 02 Oct 2019 04:31:10 GMT

Perhaps this?

Max({<PDate_7_Days={1}>} TOTAL <PDate> aggr(sum({<PDate_7_Days={1}>} SumCounter),[PDate],[Name]))

Re: get the max value after aggr....

RheaQv12 — Wed, 02 Oct 2019 16:02:09 GMT

Thank you Anil, i got the expression fixed to working...but i have a scenario where the layout has dates like start date and end date - when these dates using calendar object are selected the above table should not get filter applied

i have the objects as below....when the start date, end date are selected they are still applying on the above table

i have this expression in the table on the top but still the ignore is not getting applied

Sum({<Published_Last_7_Days = {"1"}, [Published Date]=, [Published End Date]=,[Published Start Date]=>} Aggr(Sum(ReadCounter), Title, [Published Date]))

the fields in pink are ignoring but for some reason it is not working...

Re: get the max value after aggr....

sunny_talwar — Wed, 02 Oct 2019 16:24:42 GMT

May be try this

Sum({<Published_Last_7_Days = {"1"}, [Published Date], [Published End Date], [Published Start Date]>} Aggr( Sum({<[Published Date], [Published End Date],[Published Start Date]>} ReadCounter) , Title, [Published Date]))

Re: get the max value after aggr....

RheaQv12 — Wed, 02 Oct 2019 16:40:14 GMT

thank you, what is the difference i thought in set analysis to ignore exclude we do like

[Published Date]=

[Published Date],

thanks again

Re: get the max value after aggr....

sunny_talwar — Wed, 02 Oct 2019 16:56:03 GMT

They mean the same thing within set analysis. = sign doesn't make a difference when you are trying to ignore selections

Re: get the max value after aggr....

RheaQv12 — Wed, 02 Oct 2019 17:20:10 GMT

thank you Sunny_Talwar;