Hey Folks,
I have a table that looks like this in excel, which was very easy to calculate:
| Snapshot Period |
Level |
First_Time |
Sales_Session |
p2g_mean |
p2g_sd |
ub |
lb |
p2g_chg_mean |
p2g_chg_sd |
p2g_chg_ub |
p2g_chg_lb |
| 4 Weeks Before Start |
Standard |
Yes |
I |
0.302577 |
0.085413 |
0.38799 |
0.217165 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Standard |
No |
I |
0.310554 |
0.083643 |
0.394197 |
0.226911 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Premium |
Yes |
I |
0.3615 |
0.087 |
0.4485 |
0.2745 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Premium |
No |
I |
0.358553 |
0.069001 |
0.427554 |
0.289552 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Students |
Yes |
I |
0.298856 |
0.083095 |
0.381952 |
0.215761 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Students |
Yes |
II |
0.533818 |
0.044476 |
0.578294 |
0.489341 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Students |
No |
I |
0.303418 |
0.087201 |
0.390619 |
0.216216 |
0 |
0 |
0 |
0 |
| 4 Weeks Before Start |
Students |
No |
II |
0.536134 |
0.036154 |
0.572288 |
0.49998 |
0 |
0 |
0 |
0 |
| 3 Weeks Before Start |
Standard |
Yes |
I |
0.425674 |
0.119147 |
0.544821 |
0.306527 |
0.113517 |
0.024084 |
0.1376 |
0.089433 |
| 3 Weeks Before Start |
Standard |
No |
I |
0.432582 |
0.121935 |
0.554517 |
0.310648 |
0.112013 |
0.025451 |
0.137464 |
0.086562 |
| 3 Weeks Before Start |
Premium |
Yes |
I |
0.477844 |
0.119243 |
0.597087 |
0.358601 |
0.115014 |
0.031082 |
0.146097 |
0.083932 |
| 3 Weeks Before Start |
Premium |
No |
I |
0.478983 |
0.104823 |
0.583806 |
0.37416 |
0.110539 |
0.025374 |
0.135912 |
0.085165 |
| 3 Weeks Before Start |
Students |
Yes |
I |
0.422101 |
0.117061 |
0.539162 |
0.30504 |
0.11319 |
0.024117 |
0.137307 |
0.089073 |
| 3 Weeks Before Start |
Students |
Yes |
II |
0.618456 |
0.05525 |
0.673705 |
0.563206 |
0.083297 |
0.011983 |
0.09528 |
0.071315 |
| 3 Weeks Before Start |
Students |
No |
I |
0.425816 |
0.126322 |
0.552139 |
0.299494 |
0.11236 |
0.026101 |
0.138461 |
0.086258 |
| 3 Weeks Before Start |
Students |
No |
II |
0.619878 |
0.046597 |
0.666475 |
0.573282 |
0.076695 |
0.013049 |
0.089744 |
0.063647 |
| 2 Weeks Before Start |
Standard |
Yes |
I |
0.622732 |
0.102832 |
0.725563 |
0.5199 |
0.141162 |
0.04295 |
0.184112 |
0.098213 |
| 2 Weeks Before Start |
Standard |
No |
I |
0.629323 |
0.104299 |
0.733622 |
0.525024 |
0.137103 |
0.042974 |
0.180077 |
0.094129 |
| 2 Weeks Before Start |
Premium |
Yes |
I |
0.670783 |
0.100269 |
0.771053 |
0.570514 |
0.125547 |
0.054608 |
0.180155 |
0.070939 |
| 2 Weeks Before Start |
Premium |
No |
I |
0.668225 |
0.09004 |
0.758265 |
0.578185 |
0.126979 |
0.04777 |
0.174749 |
0.079209 |
| 2 Weeks Before Start |
Students |
Yes |
I |
0.619941 |
0.10156 |
0.721501 |
0.518381 |
0.142236 |
0.042769 |
0.185004 |
0.099467 |
| 2 Weeks Before Start |
Students |
Yes |
II |
0.737712 |
0.045071 |
0.782783 |
0.692641 |
0.119404 |
0.023435 |
0.14284 |
0.095969 |
| 2 Weeks Before Start |
Students |
No |
I |
0.623961 |
0.110578 |
0.734539 |
0.513383 |
0.138739 |
0.042742 |
0.181481 |
0.095997 |
| 2 Weeks Before Start |
Students |
No |
II |
0.737245 |
0.034135 |
0.77138 |
0.70311 |
0.114146 |
0.018595 |
0.132741 |
0.095551 |
| 1 Week Before Start |
Standard |
Yes |
I |
0.832111 |
0.113899 |
0.946011 |
0.718212 |
0.177655 |
0.030583 |
0.208238 |
0.147072 |
| 1 Week Before Start |
Standard |
No |
I |
0.838953 |
0.103842 |
0.942795 |
0.735112 |
0.175706 |
0.032268 |
0.207974 |
0.143438 |
| 1 Week Before Start |
Premium |
Yes |
I |
0.890065 |
0.081181 |
0.971245 |
0.808884 |
0.153537 |
0.060521 |
0.214058 |
0.093017 |
| 1 Week Before Start |
Premium |
No |
I |
0.861749 |
0.059263 |
0.921012 |
0.802486 |
0.157673 |
0.046453 |
0.204126 |
0.111221 |
| 1 Week Before Start |
Students |
Yes |
I |
0.829287 |
0.114446 |
0.943733 |
0.71484 |
0.178909 |
0.029658 |
0.208567 |
0.149251 |
| 1 Week Before Start |
Students |
Yes |
II |
0.87827 |
0.032433 |
0.910702 |
0.845837 |
0.139089 |
0.023053 |
0.162143 |
0.116036 |
| 1 Week Before Start |
Students |
No |
I |
0.836125 |
0.116801 |
0.952927 |
0.719324 |
0.178556 |
0.030702 |
0.209258 |
0.147854 |
| 1 Week Before Start |
Students |
No |
II |
0.879335 |
0.025229 |
0.904564 |
0.854106 |
0.140005 |
0.021519 |
0.161524 |
0.118485 |
All of these statistics are based on data from two tables: total sales and budgets. I've omitted three columns and ~ 200 rows for formatting reasons. These are the additional columns fiscal year, campaign session (there are 6 distinct values), and product name (there are 5 distinct values).
The snapshots represent the time period before the start of a campaign, and we have three more snapshots: Start Date, Census Date, and End Date. What I need to do is replicate these calculations in a Qlik Sense chart so that we can show the mean of our sales vs target, the upper and lower bounds (by one standard deviation up or down), the change from the previous snapshot, and the change to the upper/lower bounds for each snapshot. Once that's done, they'll all go on a line chart for trajectories as we approach the end of a campaign.
I've been able to create a column that aggregates the total sales by snapshot period. When I try to get the total budget amounts for the same, in order to calculate the p2g mean, sd, and the changes, I am getting a blank (error).
Here's what is working for the total sales by snapshot period:
aggr(nodistinct sum({<[Program Enrollment Status]={'ELIGBLE','COMPLET'}>}[Gross Sales]),[Snapshot Name])
Here's what doesn't work for the sales target by snapshot period:
aggr(nodistinct sum([Sales Target]),[Snapshot Name])
Here's what doesn't work for the total sales standard deviation by snapshot period:
stdev(aggr(nodistinct sum({<[Program Enrollment Status]={'ELIGBLE','COMPLET'}>}[Gross Sales])/sum([Sales Target]),[Snapshot Name]))
Once I do have the SD, I want to add or subtract it from the mean, and then show the change from one snapshot to the next (above, maybe? Not sure).
Any ideas?
93/93
