Comparing data from selected month and previous month using Two tables and One Filter Pane

[su_pyae]

Hi, 

I want to compare the data from selected month and previous month. If July is selected, I want one table to show data from July, and another table to show data from June. 

The two tables are from different sources. Both tables are now showing data for selected month. 

Thank you for your time! 

[Andrei_Cusnir]

Hello,

If my understanding is correct, you would like to have two different visualizations (e.g. Table charts) and when you select e.g. the Month July, you will see the details on Table 1 for the month of July and details on Table 2 for the month of June.

Although, there are probably different ways to achieve this, I would suggest the following workaround:

1. Instead of Filter pane object, use Variable input object.
2. First create a new variable: varSelectedMonth and definition "1" (The 1 indicates the default selected month where in this occasion is January)
3. Under Appearance > Variable choose the "varSelectedMonth" for "Name" and "Drop down" option under "Show as"
4. Then you can create a new Variable input object and go to Appearance > Values
5. Change the "Fixed or dynamic values" to "Dynamic"
6. And as expression use: "='1~Jan|2~Feb|3~Mar|4~Apr|5~May|6~Jun|7~Jul|8~Aug|9~Sep|10~Oct|11~Nov|12~Dec'". The labels will be Jan, Feb Mar etc. but when selected the variable will update to 1, 2, 3 etc.
7. Now you have:
8. 
9. After that you can create a new table:
10. As Dimension 1 use expression: "=If('$(varSelectedMonth)'=Month(DATE_FIELD), Date(Date_FIELD), Null())". This expression will allow you to show the dates whose months equal to the selected month.
11. If you want to add more dimensions or measures, then you can do so with similar logic: "=If('$(varSelectedMonth)'=Month(DATE_FIELD), ANOTHER_FIELD, Null())" Where ANOTHER_FIELD, you can replace with anything else you need (e.g. field, expression etc,).
12. At this point you have a table that shows only the rows for selected month:
13. 
14. Now you can create another table with the following expressions:
15. Expression: "=If('$(varSelectedMonth)'-1=Month(DATE_FIELD_2), Date(DATE_FIELD_2), Null())". Which will show you only the dates where the month is equal to the previous month of the selected one.
16. Another expression: "=If('$(varSelectedMonth)'-1=Month(DATE_FIELD_2), ANOTHER_FIELD_2, Null())"

Here are some examples:

As you can see, when you select the month "Oct" then the first table only shows dates for October and the second table only shows dates for September. While when you select "Sep" as a month, then the first table shows only the dates for September and the second table shows only the dates for August.

I hope that this information was helpful! 

[Anku]

Hi @su_pyae 

You can compare it by using step back identifiers ie $1.

There is an alternative as well by using alternate state, which by default is inherited.

Create two filters and table:

Both the filters and tables consists similar data. Any selection will impact all.

So, to prevent these changes, go to master item create new alternate state and assign it to the filters on right side.

Change the state of table as well.

Now perform your comparison

 it is the most easiest way.

Thanks

 

[su_pyae]

Hi Andrei, 

Thank you so much for such a detailed answer. 
I followed your instructions but it is not working. 

In the table where I want data for previous month, I have a month column. And I used following formula:

= If ('$(varSelectedMonth)'-1 = Month, Month) 

But it is not giving me anything. 

Thank you so much for your help!

[su_pyae]

Hi Anku, 

Thank you for your help. I haven't tried your method yet because I am trying to make it work with one filter for now. I will update you when I have to try your method. 

[Andrei_Cusnir]

Hello,

The reason why it is not working, is probably related to the data of field Month that you have. For my use case scenario to work lets assume this sample dataset:

Date | Amount | ID

9/20/2021 | 100 | ID1

10/20/2021 | 200 | ID2

7/20/2 | 300 | ID3

So in this case you will have to use the following 3 measure expressions:

1. =If('$(varSelectedMonth)'-1=Month(Date), Date(Date), Null())
2. =If('$(varSelectedMonth)'-1=Month(Date), Amount, Null())
3. =If('$(varSelectedMonth)'-1=Month(Date), ID, Null())

As you can see the first field is always Date and the second field is the one that I want to present in the table.

In your case, what values do you have for the field Month ? 

Can you share a sample of the values (e.g. 1 or 2 values to see what you have)

[su_pyae]

I have 04, 05, 06, 07 as Month column. It has the following tags: $numeric, $integer. 

[Andrei_Cusnir]

Hello,

Thank you for sharing this information. Just out of curiosity, can you please try the Num#() [1] function?

So in your use case scenario it will be "= If ('$(varSelectedMonth)'-1 = Num#(Month), Month)". What is the observed behavior? Are you getting any data at all but it is wrong? Or do you get an error? I am just trying to understand what exactly "But it is not giving me anything." means.

Also, in your dataset how is the Month field created? Did you create a new calculated field, using the function Month() when loading the data, or was it part of the dataset already? In case the filed Month was part of the dataset, are the Months counted from 0 or from 1? e.g. Is January represented by the value 0 or the value 1 etc.

This information will help us understand better the use case scenario and how exactly is the dataset structured, so we might be able to adjust the expression accordingly.

---

[1] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati...  

[su_pyae]

Hi, 

Thank you for your detailed reply. I have used Num#() but I am still getting nothing. 

By nothing, I mean I am getting one row of -. 

When I use this formula = If ('$(varSelectedMonth)'-1 = Num#(Month), Month, 1) --> it will give me 1. 

That's what I meant by I am not getting anything because I was using this formula: = If ('$(varSelectedMonth)'-1 = Num#(Month), Month)

The files were saved with month and year. So, they were created by using right() and left() as Month and Year. 

April is 04, May is 05. 

Thank you for your time. 

[Andrei_Cusnir · Accepted solution]

Hello,

Thanks for clarifying this information. I think I was able to find where exactly is the issue. If you use the expression "'$(varSelectedMonth)'-1" the result is e.g. 7, but if you use the expression Num#('02') the result is 02. Since your months are represented by the values 01, 02, 03 etc. this means that 7 is not equal with the 07. So the IF() statement, always returns 0 for false and thus the result is always Null().

In this case, we have to make both sides to have the same format. Either both sides will compare the values 1, 2, 3 etc. or both sides have to compare the values 01, 02, 03 etc. Therefore, in this case to convert the number from e.g. '01' to '1', we have to use the function Num() [1] instead of Num#() [2].

As you can see in my example I have used the following Dimensions expressions (Please add them as dimensions expressions and not as Measure expressions):

For table with currently selected month:

• =If('$(varSelectedMonth)'=Num(TABLE1_MONTH_FIELD), Date(TABLE1_DATE_FIELD), Null())
• =If('$(varSelectedMonth)'=Num(TABLE1_MONTH_FIELD), TABLE1_MONTH_FIELD, Null())
• =If('$(varSelectedMonth)'=Num(TABLE1_MONTH_FIELD), TABLE1_AMOUNT_FIELD, Null())

For the table with the previous month:

• =If('$(varSelectedMonth)'-1=Num(TABLE2_MONTH_FIELD), Date(TABLE2_DATE_FIELD), Null())
• =If('$(varSelectedMonth)'-1=Num(TABLE2_MONTH_FIELD), TABLE2_MONTH_FIELD, Null())
• =If('$(varSelectedMonth)'-1=Num(TABLE2_MONTH_FIELD), TABLE2_AMOUNT_FIELD, Null())

And this is the outcome that I get:

As you can see, my months are also represented by numbers e.g. 01, 02, 03 etc. and it is still working!

I hope that this information was helpful

---

[1] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/FormattingFu...

[2] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati....