topic Aggr max sales date with condition in QlikView

Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 05:24:20 GMT

I have table with sales, divided by sales points.

in first expression i calculate max date, where sales > 0

max({<[Quantity]={">0"}>}Date)

in second expression i calculate sales with date, where sales > 0

sum({<Data={"$(=max({<[Quantity]={'>0'}>}TOTAL <SPoint> Data))"} >}[Quantity])

but in the second expression filter {<Data={"$(=max({<[Quantity]={'>0'}>}TOTAL <SPoint> Data))"} >} is not aggregated by Spoints, it's the same for all of them.

The necessary result for me:

Sales for point 6 with 21.07.2014

Sales for point 8 with 21.07.2014

Sales for point 12 with 18.07.2014

See attached file.

Thank you!

Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 05:32:30 GMT

Date instead Data in second expr.

Re: Aggr max sales date with condition

Thu, 24 Jul 2014 06:13:00 GMT

Hi,

Is this the same way u want to get desire output,

PFA

Re: Aggr max sales date with condition

aveeeeeee7en — Thu, 24 Jul 2014 06:16:24 GMT

To pick the Max Date try something like this:

SUM({<DateField={'$(=Max(DateField))'}>}Quantity)

Regards

Aviral Nag

Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 06:23:48 GMT

ok.

in your example:

in first expr you're calculating max date, where quantity > 0 , yes ?

in second expr you're aggregating quantity by sales points, but without date condition from previous expr.

in second expr i need to get quantity, connected with max date, where sales >0

Thank you!

Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 06:25:33 GMT

this expr is useful, but not full correct for me.

i need to get not only the quantity with max date.

max date must be such, where quantity > 0

thank you!

Re: Aggr max sales date with condition

Thu, 24 Jul 2014 06:30:38 GMT

You can mention in exp as,

aggr(sum({<[Quantity]={">0"}>}Quantity),Spoint)

Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 06:41:12 GMT

Thank you.

But this expression get me aggregated quantity > 0 for all period, but i need the same, but for max date, where quantity > 0

Re: Aggr max sales date with condition

aveeeeeee7en — Thu, 24 Jul 2014 06:46:40 GMT

Try this:

sum({<DateField={'$(=Max(DateField))'},Quantity={">0"}>}Quantity)

Re: Aggr max sales date with condition

sasikanth — Thu, 24 Jul 2014 06:59:19 GMT

try some thing like below

SUM({<DateField={'$(=Max(DateField))'}>} if(Quantity>0,Quantity))

Re: Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 07:50:05 GMT

this expression get quantity for the same data (22.07.2014).....

in your expression condition with max data isn't connected with condition, where quantity must be > 0

Re: Aggr max sales date with condition

lironbaram — Thu, 24 Jul 2014 08:38:29 GMT

first of all , set analysis won't work for you ,

the reason is that set analysis always return the same value to all rows in the chart , because set analysis defines a selection in the data model and as with all selections they effect all data the same

attach is an example using firstsortedvalue hope , it will help you

Re: Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 11:41:01 GMT

It's working!

i'd been looking for correct answer for a long time.

Thank you!)

Re: Re: Aggr max sales date with condition

Anonymous — Thu, 24 Jul 2014 12:24:36 GMT

i have one question for you.

it's working, when i show information by sales point.

but when i add to dimensions new dimensions (sales points include product type), values are not correct

in other words, i want to show quantity by product types, which are included to each sales points

Thank you!