Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

alexis
Not applicable

Multiple calendar and aggregation problem

Hi all,

I am sure this is a common problem - let me explain. In the diagram below you can see that I am using 3 separate calendars one each for Deliveries, Orders and Shipments.

MultipleCalendars.jpg

In the "blue" table (below) I am combining "Orders" (from the Order Calendar) and "Deliveries" from the "Delivery Calendar" (both shown in yellow) using the Dimension "Month Year" from an unconnected calendar called "Calendar" (shown in pink above) and then using syntax of the type:

aggr(

    if(MonthYear = [Order MonthYear],

        sum(Orders.Counter)

    ) ,MonthYear ,[Order MonthYear]) for "Orders" and


aggr(

    if(MonthYear = [Delivery MonthYear],

        sum(Orders.Counter)

    ) ,MonthYear ,[Delivery MonthYear]) for "Deliveries"

MultipleCalendarsExample.jpg

So far so good - all works fine and the numbers are correct.

Choosing a "Country" from the list box yields the correct results in the blue table.

My requirement, however, is to extend the "blue" table to show Orders and Deliveries for each country for every month (see pink table above). The resultant pivot table is incorrect - any help would be most appreciated. I attach the above demo application with data.

Best regards

Alexis

1 Solution

Accepted Solutions
bhagirath
Not applicable

Re: Multiple calendar and aggregation problem

Hi ,

Can you check the attached qvw hope this solves your problem

3 Replies
bhagirath
Not applicable

Re: Multiple calendar and aggregation problem

Hi ,

Can you check the attached qvw hope this solves your problem

alexis
Not applicable

Re: Multiple calendar and aggregation problem

As simple as that - and here was me looking for a more complex solution.

Thank you very much Bhagirath.

Best regards

Alexis

bhagirath
Not applicable

Re: Multiple calendar and aggregation problem

that's because your aggr function was running on only one dimension and in chart you were looking for data with respect to both the dimensions..

Good to know that this solves your problem...

Cheers...!!!

Bhagirath