13 Replies Latest reply: Mar 4, 2016 3:45 PM by Sunny Talwar

# 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.

• ###### Re: Combining multiple conditions on a measure

Try this:

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

• ###### Re: Combining multiple conditions on a measure

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?

• ###### Re: Combining multiple conditions on a measure

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)

• ###### Re: Combining multiple conditions on a measure

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*

• ###### Re: Combining multiple conditions on a measure

Hi,

Try like this

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

Hope this helps you.

Regards,

jagan.

• ###### Re: Combining multiple conditions on a measure

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

• ###### Re: Combining multiple conditions on a measure

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

• ###### Re: Combining multiple conditions on a measure
 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

• ###### Re: Combining multiple conditions on a measure

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:

Month(YearMonth) as Month,

*;

LOAD Date(Date#(Period, 'YYYYMM'), 'YYYYMM') as YearMonth,

Amount,

Cumulative_amt

FROM

(html, codepage is 1252, embedded labels, table is @1);

• ###### Re: Combining multiple conditions on a measure

This works fine, but I don't understand what happens when I use year or month instead of yearmonth.

• ###### Re: Combining multiple conditions on a measure

Why do you want to use Year and Month when YearMonth together is working for you?

• ###### Re: Combining multiple conditions on a measure

It didn't look that great from UI point of view. If you have any more insights, please do share.

Thank you so much for all your help.

• ###### Re: Combining multiple conditions on a measure

Just so you know, you can still make selections in Year and Month fields. The expression will still give you the right output.