Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
camilo
Contributor III
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
swuehl
MVP
MVP

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

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

Thanks Stefan, in deed it works perfectly!

camilo
Contributor III
Contributor III
Author

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)

swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

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

swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

Thanks Stefan, now I undersantd

Not applicable

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.