topic Re: Set analysis help in QlikView

Set analysis help

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

Hi Experts,

Can any one please help me on below requirement.

I have build Monthly Trend chart in the below image with expression

=Sum(Aggr((Sum({<Type={'A'}>}Amount)*Avg(A1%)),Date,Dept,Branch))

This chart contains Monthly Total Amount.

In the second chart i.e., Monthend Trend need to show each month Max(Date) Total amount in the trend instead of all the dates Total Amount in each month.

I have tried below expression for only Monthend Trend in each month

=Sum(Aggr((Sum({<Type={'A'},Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}Amount)*

Avg({<Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}A1%)),Date,Dept,Branch))

But only Sep Month Max(Date) Total amount getting correctly remaining months showing Zero values.

Please help on this.

Thanks in advance.

Re: Set analysis help

tresB — Fri, 21 Sep 2018 06:18:09 GMT

Don't use set analysis for this, try using 'if' statement instead. Set analysis gets evaluated once for a chart and you can't make it work row-wise even using aggr().

Re: Set analysis help

mahitham — Fri, 21 Sep 2018 06:20:27 GMT

Hi tresesco,

Thanks for your reply.

Could you please help me how to use below set analysis in if statement in my scenario

=Sum(Aggr((Sum({<Type={'A'},Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}Amount)*

Avg({<Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}A1%)),Date,Dept,Branch))

Re: Set analysis help

jyothish8807 — Fri, 21 Sep 2018 06:34:09 GMT

May be like this:

Dimension: Month(Date)

if(Date=Max(Date), sum({Type={'A'}>}Amount)*Avg(A1%))

Br,

Re: Set analysis help

tresB — Fri, 21 Sep 2018 06:36:36 GMT

Could you share your sample app?

Re: Set analysis help

mahitham — Fri, 21 Sep 2018 06:50:50 GMT

Hi jyothish8807,

Thanks for your reply.

After applying the above expression I am getting only Jun value and remaining months showing null.

when aggr by Date,Dept,Branch removed, then Jun value also getting wrong so modified the expression like below

=if(Date=Max(Date),Sum(Aggr((Sum({<Type={'A'}>}Amount)*Avg(A1%)),Date,Dept,Branch))

then getting Jun value correct but remaining months showing null.

Last time September value getting now only jun value getting in trend.

In the below expression Sep value is getting correctly and remaining months showing zero but when I have applied filter on those months those are showing values instead of zero.

=Sum(Aggr((Sum({<Type={'A'},Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}Amount)*

Avg({<Date={'$(=Date(Max(Date),'DD/MM/YYYY'))'}>}A1%)),Date,Dept,Branch))

Re: Set analysis help

jyothish8807 — Fri, 21 Sep 2018 08:41:31 GMT

Hi Mahitha,

Try this sample

Br,

Re: Set analysis help

jyothish8807 — Fri, 21 Sep 2018 08:42:53 GMT

Load

MonthName(Date) as MonthYear,

Date,

Sale;

LOAD * INLINE [

Date, Sale

1/22/2018, 10

1/25/2018, 15

2/21/2018, 21

2/27/2018, 11

];

Load

MonthYear,

date(max(Date),'M/D/YYYY') as New_Date

resident A

group by MonthYear;

UI:

Dimension: MonthYear

Exp: sum({<Date=p(New_Date)>}Sale)

Br,

Re: Set analysis help

tresB — Fri, 21 Sep 2018 09:38:28 GMT

Use Expression:

Sum(if([Reporting Date]=Aggr(nodistinct Max( [Reporting Date]),Month), Aggr((Sum({<Type={'A'}>}MTM)*

Avg(CalColumn1Percent)),Month,[Reporting Date],Name,Folder)))

Re: Set analysis help

mahitham — Fri, 21 Sep 2018 09:42:48 GMT

Thanks a lot jyothish8807

Its working in my scenario like below

=Sum(Aggr((Sum({<Type={'A'},Date=p(New_Date)>}Amount)*

Avg({<Date=p(New_Date)>}A1%)),Date,Dept,Branch))

Re: Set analysis help

mahitham — Fri, 21 Sep 2018 09:44:03 GMT

Thanks a lot tresesco

Its working.