Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a measure which has to show data based specific accounts and sum it all up to the date selected in filter.
I have created this so far and I don't understand how to combine these two expressions in the same measure
1. Sum({$<account = {1,2,3,}>},amt)
2. Sum({$<year = {=max(year)}>},amt)
I tried creating variable for one of the conditions and still couldn't get this to work. Can someone please correct me.
Have you made a selection in date field? May be you need to restrict your expression to not change based on selection in date field:
Sum({$<account = {1,2,3}, year = {$(=max(year))}, date = , month = , monthyear = , Quarter = >} amt)
Try this:
Sum({$<account = {1,2,3}, year = {$(=max(year))}>} amt)
Unfortunately this din't work.
Sum({$<account = {1,2,3}>} amt)
this formula shows correct information but only for current date, but if I have to see the same amount for say dec 2013, I see only the records belonging to Dec 2013 and not all records summed till Dec 2013.
What should I do to sum all information till the date selected by the filter?
Have you made a selection in date field? May be you need to restrict your expression to not change based on selection in date field:
Sum({$<account = {1,2,3}, year = {$(=max(year))}, date = , month = , monthyear = , Quarter = >} amt)
I tried this as well.
I completely removed the account selection and now I just want to convert the following query to qlik expression
select SUM(amt) from t1 where date <= *date selection from the filter*
Hi,
Try like this
Sum({$<year = , date ={'<=$(=Date(Max(Date)))'}, month = , monthyear = , Quarter = >} amt)
Hope this helps you.
Regards,
jagan.
This still gives me the value for selected time frame and not the entire cumulative sum
Is there any way you can share a sample with us Akshata?
Period | Amount | Cumulative_amt |
201301 | -957005.99 | -957005.99 |
201302 | 1798565.62 | 841559.63 |
201303 | -787515.08 | 54044.55 |
201304 | -23110.8325 | 30933.7175 |
201305 | 617409.37 | 648343.0875 |
201306 | 1135189.38 | 1783532.468 |
201307 | 1051420.77 | 2834953.238 |
201308 | 966678.0871 | 3801631.325 |
201309 | -140738.1417 | 3660893.183 |
201310 | 1001824.219 | 4662717.402 |
201311 | 1019397.285 | 5682114.688 |
201312 | 1036970.351 | 6719085.039 |
201401 | 1054543.418 | 7773628.457 |
201402 | 1072116.484 | 8845744.94 |
201403 | 1089689.55 | 9935434.49 |
201404 | -957005.99 | 8978428.5 |
201405 | 1798565.62 | 10776994.12 |
201406 | -787515.08 | 9989479.04 |
201407 | -23110.8325 | 9966368.207 |
201408 | -46636.29433 | 9919731.913 |
201409 | -70161.75617 | 9849570.157 |
201410 | -93687.218 | 9755882.939 |
201411 | -117212.6798 | 9638670.259 |
201412 | -140738.1417 | 9497932.117 |
This is what my data looks like. I have created year_Name and Month_Name based on period column and use them to filter my data.
My end goal is to show 9755882.93 when the user selects Oct, 2014 and show 9497932.11 when there is no filter. but when I use the max condition, I am getting the data only for 2014 Jan to 2014 Oct
This expression seems to work for the sample data provided:
=Num(Sum({<YearMonth = {"$(='<=' & Date(Max(YearMonth), 'YYYYMM'))"}, Year, Month>}Amount), '#,###.00')
No Selection
201410 Selected
Script:
Table:
LOAD Year(YearMonth) as Year,
Month(YearMonth) as Month,
*;
LOAD Date(Date#(Period, 'YYYYMM'), 'YYYYMM') as YearMonth,
Amount,
Cumulative_amt
FROM
[https://community.qlik.com/message/990029?et=watches.email.thread#990029]
(html, codepage is 1252, embedded labels, table is @1);