topic Re: Monthy Avg by Day in QlikView

Monthy Avg by Day

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

Hi All, I have an chart Forecast by Day. One KPI would be Monthly Avg Forecast by Day.

That is: sum(Forecast for each month)/Working day for each Month.

And the bar will be a straight line for one each Month.

I am attaching the App. Could you please help me to figure out Month Level Forecast.

Thanks,

Sarif

Re: Monthy Avg by Day

sunny_talwar — Thu, 22 Jun 2017 11:24:58 GMT

You need this in a text box object? May be try this

=Sum(SalesForecastUnitsSnapshot/WorkingDays)

Re: Monthy Avg by Day

mhmmd_srf — Thu, 22 Jun 2017 11:31:33 GMT

No Sunny,

I need this in the chart.

In chart I have monthly level forecast in Green Line.

I need in this.

I am using below:

sum(aggr(sum(SalesForecastUnitsSnapshot),CstFiscalPeriod))/WorkingDays

But if I select one month it showing one dot. I need a straight line for one month.

Please help.

Thanks,

Sarif

Re: Monthy Avg by Day

sunny_talwar — Thu, 22 Jun 2017 11:36:28 GMT

May be this

=Sum(TOTAL Aggr(Sum(SalesForecastUnitsSnapshot),CstFiscalPeriod))

Re: Monthy Avg by Day

sunny_talwar — Thu, 22 Jun 2017 11:38:32 GMT

Or may be this

=Sum(Aggr(Sum(TOTAL <CstFiscalPeriod> SalesForecastUnitsSnapshot),CstFiscalPeriod, CstDate))

Re: Monthy Avg by Day

mhmmd_srf — Thu, 22 Jun 2017 11:44:59 GMT

Yes.. I was trying use TOTAL, but could not use it Properly.

Thanks Sunny.

I am using :

=Sum(TOTAL Aggr(Sum(SalesForecastUnitsSnapshot),CstFiscalPeriod))/WorkingDays

Now,

for Month1: 388897

for Month2: 423599

For individual Month selection it is showing Correct. But If I select both Month 1 and 2, values are changing.

Might be Working days is not restricting for Month.

Could you please help on this.

Thanks,

Sarif

Re: Monthy Avg by Day

sunny_talwar — Thu, 22 Jun 2017 11:48:09 GMT

WorkingDays were not even there in your expression.... Try this

=Sum(Aggr(Sum(TOTAL <CstFiscalPeriod> SalesForecastUnitsSnapshot/WorkingDays),CstFiscalPeriod, CstDate))

Re: Monthy Avg by Day

mhmmd_srf — Thu, 22 Jun 2017 11:56:43 GMT

WOW.... great.. I am just wondering why I am not able to do this by my own...

You are awesome.

Can you please help me to understand the function of <CstFiscalPeriod> in your expression.

Thanks,

Sarif

Re: Monthy Avg by Day

sunny_talwar — Thu, 22 Jun 2017 11:58:31 GMT

It is used with TOTAL Qualifier which tells the expression the scope of the aggregation.

The Aggregation Scope