Create bar chart using Set Analysis and Aggr Function

[sabirovss]

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.

[dplr-rn]

You would need as of table

check below

The As-Of Table

[alexander_ratan]

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