topic Re: How to create/Get YTD average for a derived expression in QlikView

How to create/Get YTD average for a derived expression

Anonymous — Thu, 06 Jul 2017 07:19:59 GMT

Good day,

I have a business requirement to present YTD % achieved based on Target value.

in this actual value is derived value expression based on daily

expression to get daily Actual value :-

=num(Avg(aGGR((Count({<TYPE={181,182},D_PK= {"=D_Date=D_min_Date"}>}DISTINCT ID_A))

/Count({<User_Type= {"Screener"}>}DISTINCTperson),Day,Month,Year)),'##0.0')

Now YTD budget :-

avg({<Type={'Production'},D_Month_Name_Num={'<=$(=max({<Flag={Y}>}D_Month_Name_Num))'},Month=>}Daily_Budget)

From above 2 expression how do I derive YTD expression??

appreciate some help on this , may this could be easy one , I am a beginner

Re: How to create/Get YTD average for a derived expression

sunny_talwar — Thu, 06 Jul 2017 16:42:56 GMT

It might be easier to help if you can share a sample for us to take a look at.

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: How to create/Get YTD average for a derived expression

Anonymous — Thu, 06 Jul 2017 18:51:15 GMT

Thank you so much for quick responds

I may make it bit more simple . a derived expression productivity on daily basis rollup to YTD

Productivity =count ID_A/count of person on Daily

Count =DISTINCT ID_A

Person=DISTINCTperson

Expression used in Application

=num(Avg(aGGR((Count(DISTINCT ID_A)) /Count(DISTINCTperson),Day,Month,Year)),'##0.0')

Now how to get YTD Average on this??

Re: How to create/Get YTD average for a derived expression

sunny_talwar — Fri, 07 Jul 2017 08:37:00 GMT

You will have Month and Day as a dimension or this was just to show how the data is laid out? Is this expression for a text box object? Try like this

=Num(Avg({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} Aggr(Count({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} DISTINCT ID_A)/Count({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} DISTINCT person), Day, Month, Year)),'##0.0')

You need to replace DateFieldFormatHere with the format of your Date field.

Re: How to create/Get YTD average for a derived expression

Anonymous — Fri, 07 Jul 2017 11:07:58 GMT

Thank you,

Its is a text value and i am having problem in the same , (i am getting YTD for Chart value,)

can you help me i have amended the script as below, but no value reflecting in text. I think I am doing something wrong ? is date format is important, how do i specify the format, do you have any eg?

=Num(Avg({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} Aggr(Count({<ID_DA_UPDATE_TYPE={12,43,45,89},DA_PK= {"=ONLY({1}DA_Date)=ONLY({<Month=>}DA_min_Date)"},Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} DISTINCT ID_DA)/Count({<User_Type= {"Screener"},Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DateFieldFormatHere') & '<=' & Date(Max(Date), 'DateFieldFormatHere'))"}>} DISTINCT PDA_Screener), Day, Month, Year)),'##0.0')

Re: How to create/Get YTD average for a derived expression

sunny_talwar — Fri, 07 Jul 2017 12:29:53 GMT

Look here for Dates in Set Analysis