topic Re: Annualized Sales for Customer & Distributors in QlikView

Annualized Sales for Customer & Distributors

Mon, 23 Nov 2015 13:00:58 GMT

Dear QV Experts,

Annualized Sales for Customer & Distributors:

The Expression for

Actual Sales:

=num(Sum({$<Date={">=$(CYStartDate)<=$(vMaxDate)"}>} CustomerSales)/1000,'$#,##0.00')

Annualized Sales,

Earlier we save the MaxSalesDate from the dataset in a variable & used it.

=num(( (Sum({$<Date={">=$(CYStartDate)<=$(vMaxDate)"}>} CustomerSales)/1000) / DayNumberOfYear(CYMaxCustDate))*DayNumberOfYear(CYEndDate),'$#,##0.00')

Now the requirement is that,

One Customer has multiple Distributors.

Selecting Customers in ListBox will display multiple Distubtors in Straight Table.

Each Distributor having different Max Sales Date, based on that Annualized Sales will Differ.

Please find the screenshot marked in Red.

Max, shows 65 days [ Which was the MaxSalesDate in the dataset]

MaxCustDate, shows different MaxSalesDate for the Distributors.

Here in the Expression, Selected Sum of Rows, so can Grand Total correctly. But in the KPI i can't able to sum up.

Can you please suggest me.

Re: Annualized Sales for Customer & Distributors

sunny_talwar — Mon, 23 Nov 2015 13:04:56 GMT

Use aggregate function to achieve what you are looking for. If I understood correctly you want to see 8,798.64 as a KPI, right? Try something like this:

Sum(Aggr(YourExpression4Annualized, YourChartDimensions))

Re: Annualized Sales for Customer & Distributors

Mon, 23 Nov 2015 13:19:22 GMT

Excellent Sunny. It works now. Great.

Re: Annualized Sales for Customer & Distributors

sunny_talwar — Mon, 23 Nov 2015 13:20:46 GMT

Awesome

Re: Annualized Sales for Customer & Distributors

Mon, 23 Nov 2015 13:34:41 GMT

Similarly,

The straight which doesn't have Distributor but with other dimensions, Segment, Item,Brand, etc

showing Annualized Sales Incorrectly.

When i include Distributor in Dimension, then i could able to view the Annualized Sales correctly.

But the requirement is not to show the Distributor in the second chart & the Annualized should be &8,798.49 .

Can you please suggest me on this.

Please find the screenshot below.

Thanks

Re: Annualized Sales for Customer & Distributors

sunny_talwar — Mon, 23 Nov 2015 13:39:57 GMT

If it is straight table, why don't you keep the CustomerName table, but just hide it from anybody seeing it. This can be done on the presentation tab:

Re: Annualized Sales for Customer & Distributors

Mon, 23 Nov 2015 14:13:00 GMT

Thanks Sunny. It works. Awesome.