How to assign the most recent cluster to historical data

[Anonymous]

Hi,

I have this problem.

I would like to assign to Customers their most recent Cluster (based on a calendar selection) and made calculation on their historical values, disregarding their previous cluster.

In attached a simple example for one Customer.

My aim is create a bar chart with Cluster as Dimension and Sum(qta actual)/Sum(qta prevoius) as expression (where actual means selected dates and prevoius means the corresponding dates of previous year).

Thanks a lot!

Elena

[Anonymous]

in this: Sum(qta actual)/Sum(qta prevoius)

you mean current year v/s previuos year?

or

Current year date v/s Previous year data?

or

Current Year Month v/s Previous year month

[Anonymous]

I mean Current Year Month v/s Previous year month

Thank you!

Elena

[Anonymous]

Take Cluster as a dimension, and expression is:

For Current Year v/s Previous Year : 

=sum({<YearField={'=$(=max(YearField)'}>} QTA)/sum({<YearField={'=$(=(max(YearField)-1)'}>} QTA)

for rest see these threads:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Comparing Current Month and Previous Month Sales

[Anonymous]

okay, see these:

Set Analysis Current Month vs Previous Month

QlikView App: Set Analysis - Prior Period Comparison

Set analysis for the sum of the current month customers

[Anonymous]

It's not a problem related to point in time reporting.

It's related to the change of cluster.... I what the most recent cluster even if I consider values of other past cluster for the same customer.

Thank you

Elena

[sunny_talwar]

First of all, your clusters are not matching up with your expected output and keeping that in mind the results won't match, but see if this helps:

This period:

=Sum({<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta)

Previous period:

=Sum({<Date ={"$(='>=' & Date(AddYears(Min(Date), -1), 'YYYYMM') & '<=' & Date(AddYears(Max(Date), -1), 'YYYYMM'))"}>}Qta)

I think I got it now. Let me see if I can figure it out.

[sunny_talwar]

Try these two expressions:

Current

=If(Cluster = FirstSortedValue(TOTAL Cluster, -Date), Sum(TOTAL{<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta))

Previous

=If(If(Cluster = FirstSortedValue(TOTAL Cluster, -Date), Sum(TOTAL{<Date ={"$(='>=' & Date(Min(Date), 'YYYYMM') & '<=' & Date(Max(Date), 'YYYYMM'))"}>}Qta)) > 0,

Sum(TOTAL {<Date ={"$(='>=' & Date(AddYears(Min(Date), -1), 'YYYYMM') & '<=' & Date(AddYears(Max(Date), -1), 'YYYYMM'))"}>}Qta))

Output Screenshots:

1)

2)

3)

[Kushal_Chawda · Accepted solution]

Please see the attached. This will help.

[sunny_talwar]

Nice way of doing it Kush