Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik people,
I'm trying create bar chart with set analysis with this data set.
Created_Date | USER_ID | |
2018-03-01 | 333 | AA |
2018-03-05 | 1111 | AA |
2018-03-28 | 1111 | BB |
2018-03-28 | 6666 | BB |
2018-05-05 | 1111 | CC |
2018-05-25 | 6666 | BB |
User have unique ID and one user have many values.
In the graph, one User can only have one Value during one month. So if it has had two Created_Date with two different Values, the latest one should be counted.
And also one thing, if user did not have a Created_Date during the last month, the latest month’s Values should be counted.
For example it should looks like below table:
Created_Date | USER_ID | Value |
March-18 | 333 | AA |
March-18 | 1111 | BB |
March-18 | 6666 | BB |
April-18 | 333 | AA |
April-18 | 1111 | BB |
April-18 | 6666 | BB |
May-18 | 1111 | CC |
May-18 | 333 | AA |
May-18 | 6666 | BB |
Could you please help to find right way to get this result?!
Thanks a lot in advance!
Kind regards,
Rinat.
Please try to use Previous() and Peek() functions for this case:
1. Modify the data - calculate a value for max Date by month and user
2. Create the full set of data for every user in order to get : user / value / month (first date of the month) and join with the main table
3. Then using functions Previous() and Peek() put the previous value for every user-month for the loaded row by user-month