Set analysis measures

[Manni_SM]

Hi all

I want to calculate below measures in qliksense cloud like by like analysis 

I have fields like :

Total sales

Orderdate

Base sales ( order quantity*product price) as sales.

Now below measures need to calculate:

1.Current month sales

2 previous month sales

3. Month on month sales variance 

4.current month vs previous month sales 

How can I do this ?

[marcus_sommer]

The simplest method is to apply 4 expressions, like:

sum({< Period = {"$(=max(Period))"}>} Sales)
sum({< Period = {"$(=max(Period)-1)"}>} Sales)
sum({< Period = {"$(=max(Period))"}>} Sales) / sum({< Period = {"$(=max(Period)-1)"}>} Sales)
sum({< Period = {"$(=max(Period))"}>} Sales) - sum({< Period = {"$(=max(Period)-1)"}>} Sales)

whereby Period is a calendar-field of: Year * 12 + Month. Important is further no to include any date-related field within the charts.

[WeLoveQlik]

I would create a Month Index using the script.

Steps:

1. In your fact table create a key that is YYYYMM (its important month is 2 digits, here)

2. Do a resident load of all distinct Values in you YearMonthKey you created in step 1

3. Load those distinct values with a ROWNO() to create an index. 

4. Create a Max(Index) Variable and all the prior period variables. For example, Max(Index)-1.  Use those in your set analysis in the same manner described by @marcus_sommer's response.

Both are very similar just done in a slightly different way!

[Manni_SM]

Shall I create a field in master calendar?

Year * 12 + Month as period ? 

[marcus_sommer]

That's my preferred way to create a continuous information to months over n years. The logic is simple and could be easily integrated within the calendar-creation. Similar approaches are also applicable by weeks and quarters, the values are immutable and it's possible to revers the calculation to get the underlying date-information again.

Methods which using a record-logic - per distinct extract and rowno/recno or within interrecord-functions in sorted resident-loads - are often more complex and require usually more efforts - especially if not only the months are flagged. But if the period-information have some kind of gaps and it's important to consider them then you would need such an approach.