Hi all,
I am new to qlik. I have a concatenated table where member and order information has been combined for performance reason. Data model works good so far from speed perspective.
I ran across a set analysis scenario that i can't figure out.
my table looks like this
| source | upload_key | order_amount | order_segment | member_month | upload_name |
| c | 1 | 12 | a | null | prior |
| c | 1 | 12 | b | null | prior |
| c | 1 | 12 | c | null | prior |
| m | 1 | null | null | 1 | prior |
| m | 1 | null | null | 1 | prior |
| m | 1 | null | null | 1 | prior |
| c | 2 | 21 | a | null | current |
| c | 2 | 21 | b | null | current |
| c | 2 | 21 | c | null | current |
| m | 2 | null | null | 1 | current |
| m | 2 | null | null | 1 | current |
| m | 2 | null | null | 1 | current |
table has two different uploads (time frames) and member and claims data (source=c,m)
I need to reports on order_segment dimension by order_amount divided by entire year's worth of member_months
This is what i have searched up and it does not work as i am thinking that order_months do not relate to order segments. Totals return numbers but order segment dimensions don't
SUM ({$<UPLOAD_KEY_year = {$(=max(UPLOAD_KEY_year) )}>} [UTILIZATION]) / SUM ({1}>} [Order_months])
My result table would look like this 12/ total member_months=3 and 21 divided by same uploads total member months=3
Can you please help.
| order segment | current order/months | previous orders/month |
| a | 4 | 7 |
| b | 4 | 7 |
| c | 4 | 7 |
