Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Valued Contributor

How to assign the most recent cluster to historical data

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted

Re: How to assign the most recent cluster to historical data

Please see the attached. This will help.

View solution in original post

10 Replies
Highlighted
Esteemed Contributor

Re: How to assign the most recent cluster to historical data

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

Highlighted
Valued Contributor

Re: How to assign the most recent cluster to historical data

I mean Current Year Month v/s Previous year month

Thank you!

Elena

Highlighted
Esteemed Contributor

Re: How to assign the most recent cluster to historical data

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

Highlighted
Esteemed Contributor

Re: How to assign the most recent cluster to historical data

Highlighted
Valued Contributor

Re: How to assign the most recent cluster to historical data

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

Highlighted

Re: How to assign the most recent cluster to historical data

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.

Highlighted

Re: How to assign the most recent cluster to historical data

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)

Capture.PNG

2)

Capture.PNG

3)

Capture.PNG

Highlighted

Re: How to assign the most recent cluster to historical data

Please see the attached. This will help.

View solution in original post

Highlighted

Re: How to assign the most recent cluster to historical data

Nice way of doing it Kush