Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Combining multiple conditions on a measure

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.

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

13 Replies
sunny_talwar

Try this:

Sum({$<account = {1,2,3}, year = {$(=max(year))}>} amt)

Not applicable
Author

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?

sunny_talwar

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)

Not applicable
Author

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*

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Sum({$<year = , date ={'<=$(=Date(Max(Date)))'}, month = , monthyear = , Quarter = >} amt)


Hope this helps you.


Regards,

jagan.

Not applicable
Author

This still gives me the value for selected time frame and not the entire cumulative sum

sunny_talwar

Is there any way you can share a sample with us Akshata?

Not applicable
Author

PeriodAmountCumulative_amt
201301-957005.99-957005.99
2013021798565.62841559.63
201303-787515.0854044.55
201304-23110.832530933.7175
201305617409.37648343.0875
2013061135189.381783532.468
2013071051420.772834953.238
201308966678.08713801631.325
201309-140738.14173660893.183
2013101001824.2194662717.402
2013111019397.2855682114.688
2013121036970.3516719085.039
2014011054543.4187773628.457
2014021072116.4848845744.94
2014031089689.559935434.49
201404-957005.998978428.5
2014051798565.6210776994.12
201406-787515.089989479.04
201407-23110.83259966368.207
201408-46636.294339919731.913
201409-70161.756179849570.157
201410-93687.2189755882.939
201411-117212.67989638670.259
201412-140738.14179497932.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

sunny_talwar

This expression seems to work for the sample data provided:

=Num(Sum({<YearMonth = {"$(='<=' & Date(Max(YearMonth), 'YYYYMM'))"}, Year, Month>}Amount), '#,###.00')

No Selection

Capture.PNG

201410 Selected

Capture.PNG

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);