Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
camilo
New Contributor III

Comparing data from two different Time Periods

Hi,

I´m having some problems trying to compare the data from two different time periods (between years). I´m using set analysis but it compares exactly the same time periods.

I would like to compare time periods with one day difference, example:

2011

From day 2 to day 20

A want to compare this period with:

2010

From day 3 to day 21

I´m upploading one example hopping it helps to clarify what I´m saying.

Thanks!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Comparing data from two different Time Periods

Hi Camilo,

I think it could look like:

=sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

   >} Sales)

Regards,

Stefan

8 Replies
Highlighted
MVP
MVP

Comparing data from two different Time Periods

Hi Camilo,

I think it could look like:

=sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

   >} Sales)

Regards,

Stefan

camilo
New Contributor III

Comparing data from two different Time Periods

Thanks Stefan, in deed it works perfectly!

camilo
New Contributor III

Comparing data from two different Time Periods

Hi Stefan,

Using your formula solved how to presents the sales info, now I´m trying to go one step forward and calculate for example the % Margin being:

%Margin= (Sales-Costs)/Sales

I´m trying to use the following but it doesn´t work:

=sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

   >} (Sales-Costs)/Sales)

MVP
MVP

Comparing data from two different Time Periods

Hi Camilo,

what do you mean with "it doesn't work"? Syntax error / returning no results at all?

Besided that I think you should use something like

= (1 -

sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

    >} Costs) /

sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

    >} Sales))

i.e. putting the sum around each portion of (1-Costs/Sales), otherwise you would sum up  all margin % of each single row, resulting in ever increasing values (could get pretty large).

camilo
New Contributor III

Comparing data from two different Time Periods

Exatlty Stefan,

I got huge numbers or I didn´t got anything, now your solution does the work but I don´t understand why it add up when I don´t use the "1-" and it works when I use it.

Thanks

MVP
MVP

Comparing data from two different Time Periods

I believe it is not depending on the "1-", that's just an equivalent expression:

(1-cost/sales) = (sales-costs)/sales

I preferred the first, because I only need two sums then instead of three.

As said, the difference between our two versions is that I first sum up Costs and Sales over all record rows and then do the Margin% calculation (1-cost/sales), instead of doing the calculation per row and then summing up.

Or have I misunderstood your question?

camilo
New Contributor III

Comparing data from two different Time Periods

Thanks Stefan, now I undersantd

Not applicable

Re: Comparing data from two different Time Periods

This calculation does not work for me. I get no results back at all. I know my dimension is valid. What should I check?

=sum({$<Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},Year=, Day=, Month=>} Cnt)

I figured out my problem was the Date needed to be the Date_Num field in my data. But I'm still confused on how this is supposed to work. Camilo, can you post your final solution? It would just nice to look and see how your graph ended like. Thanks.

Community Browser