Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am struggling to put together a formula and was wondering if anyone has any ideas.
I have data such as the below (I've also attached the excel file with formulas for reference). The columns I actually have in my data are [Month Year],[Year],[Month],[Days_In_Month],[Inflows_Outflows],[Rolling_Total]. I want to be able to dynamically calculate Weighted_Total in Qlik.
Month Year | Year | Month | Days_In_Month | Inflows_Outflows | Rolling_Total | Weighted_Total |
Jan-19 | 2019 | Jan | 31 | 5,000,000.00 | 186,123,300.00 | 186,123,300.00 |
Feb-19 | 2019 | Feb | 28 | - | 186,123,300.00 | 186,123,300.00 |
Mar-19 | 2019 | Mar | 31 | (489,699.00) | 185,633,601.00 | 185,954,625.90 |
Apr-19 | 2019 | Apr | 30 | - | 185,633,601.00 | 185,874,369.68 |
May-19 | 2019 | May | 31 | - | 185,633,601.00 | 185,824,940.34 |
Jun-19 | 2019 | Jun | 30 | 250,000.00 | 185,883,601.00 | 185,834,663.10 |
Jul-19 | 2019 | Jul | 31 | - | 185,883,601.00 | 185,841,819.12 |
Aug-19 | 2019 | Aug | 31 | - | 185,883,601.00 | 185,847,149.32 |
Sep-19 | 2019 | Sep | 30 | - | 185,883,601.00 | 185,851,155.00 |
Oct-19 | 2019 | Oct | 31 | (100,000.00) | 185,783,601.00 | 185,844,266.27 |
Nov-19 | 2019 | Nov | 30 | - | 185,783,601.00 | 185,838,817.29 |
Dec-19 | 2019 | Dec | 31 | - | 185,783,601.00 | 185,834,127.69 |
The Weighted_Total column is calculated using a Weighted Average of the Rolling_Total and Days in Month which is then rolled along through the remainder of the months.
My goal is to be able to select one Month Year in Qlik and have a formula for a KPI that calculated the Weighted_Total.
@sunny_talwar was wondering if you had any ideas
Thanks!
Mark
Hi,
You can use Set Analysis for the measure. For example, try the KPI measure below:
=If(GetSelectedCount([Month Year]) = 1,
Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Rolling_Total * Days_In_Month) / Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Days_In_Month)
,'Please select one MonthYear')
Hope this helps,
BR,
Vu Nguyen
Hi,
You can use Set Analysis for the measure. For example, try the KPI measure below:
=If(GetSelectedCount([Month Year]) = 1,
Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Rolling_Total * Days_In_Month) / Sum({<[Month Year] = {"<=$(=[Month Year])"} >} Days_In_Month)
,'Please select one MonthYear')
Hope this helps,
BR,
Vu Nguyen