topic Re: using two expressions as one expression in bar chart in QlikView

using two expressions as one expression in bar chart

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

i have two expressions in bar chart

=sum({<IsSameMonth={'1'}>}sales)

=sum({<IsRolling6={'1'} ,IsPr={'1'} >}sales)

i want to convert these two expressions into a single expression is it possible

Re: using two expressions as one expression in bar chart

sunny_talwar — Wed, 26 Oct 2016 18:39:05 GMT

Yes, you should be able to use a synthetic dimension or Island table as dimension and use Pick(Match()) to display both these expression within a single expression

Re: using two expressions as one expression in bar chart

Wed, 26 Oct 2016 18:42:44 GMT

Hi Sunny can u please provide me an example please

i'm struggling with this since the last 1 week and your help will be highly appreciated

Re: using two expressions as one expression in bar chart

sunny_talwar — Wed, 26 Oct 2016 20:29:43 GMT

Hahahahha, John it would be nice if you can provide me with what you have and I can show how it can be done . It will be time consuming for me to create a sample and then even after I do create one, we won't know if it matches your requirement on not. I don't have that sort of time my friend. Please help me help you by providing me a sample where I can help you out.

Best,

Sunny

Re: using two expressions as one expression in bar chart

Anonymous — Wed, 26 Oct 2016 20:36:44 GMT

=sum({<IsSameMonth={'1'}, IsRolling6={'1'} , IsPr={'1'} >} sales) gives you sum of sales for all three conditions

sum({<IsSameMonth={'1'}>} + <IsRolling6={'1'}> + <IsPr={'1'}> } sales) gives common values....

Re: using two expressions as one expression in bar chart

Wed, 26 Oct 2016 20:37:13 GMT

Hi Sunny

My problem started with this and going on

Top two values in current month comparing the same values with previous month

I'm struggling to get this solution

there is a solution in place which fits me

but

1. i'm getting multiple legends for each month i want a single consolidated legend

2. the colour of the bars if the same country is in both the months it should have same colour

please help me out

Re: using two expressions as one expression in bar chart

sunny_talwar — Wed, 26 Oct 2016 20:44:48 GMT

Wow this is from a while ago. I am willing to help John, all I need is a sample (with actual or dummy data), but something to work with

Re: using two expressions as one expression in bar chart

Wed, 26 Oct 2016 20:57:51 GMT

Hi Sunny this is how i have written my code in the data model

a:
LOAD * INLINE [
    Country, Date, Value
    Argentina,Jul2016,650
    Japan,Jul2016,300
    America,Jul2016,200
    Argentina,Jun2016,550
    Japan,Jun2016,200
    America,Jun2016,100
    America,May2016,300
    Argentina,May2016,250
    Japan,May2016,150
    America,Apr2016,200
    Argentina,Apr2016,170
    Japan,Apr2016,210
];

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident keytable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

tmpAsOfCalendar:
Load distinct Month1 Resident MasterCalendar ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month1 as AsOfMonth
Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month1,
AsOfMonth,
-([AsOfMonth]<>[Month1]) as [IsPrev51],
Round((AsOfMonth-Month1)*12/365.2425) as MonthDiff,
// Date(Date#(MonthYear,'MMMYYYY'),'YYYYMM') as MonthYearq,
Year(AsOfMonth)-Year(Month1) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month1;

Drop Table tmpAsOfCalendar;

below is my output

and the problem with this is the legend is not worthy if i'm comparing top5 then it is becomoing clumpsy

and the colour of the same countries should be same in different months where as it is not

please help me out

Re: using two expressions as one expression in bar chart

sunny_talwar — Wed, 26 Oct 2016 21:00:16 GMT

Give me some time to digest this and I will try to get back to you as soon as I can.

Re: using two expressions as one expression in bar chart

Wed, 26 Oct 2016 21:01:07 GMT

ok sunny please dont forget

Re: using two expressions as one expression in bar chart

sunny_talwar — Wed, 26 Oct 2016 23:36:59 GMT

Are you trying to get this/

Script used:

LOAD Country,

Date(Date#(Date, 'MMMYYYY')) as Date,

Value;

LOAD * INLINE [

Country, Date, Value

Argentina,Jul2016,650

Japan,Jul2016,300

America,Jul2016,200

Argentina,Jun2016,550

Japan,Jun2016,200

America,Jun2016,100

America,May2016,300

Argentina,May2016,250

Japan,May2016,150

America,Apr2016,200

Argentina,Apr2016,170

Japan,Apr2016,210

];

Temp:

Load min(Date) as minDate,

max(Date) as maxDate

Resident a;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

LOAD TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

MonthName(TempDate) as MonthYear

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Dimension

MonthYear

Country

Expression:

=If(Rank(Sum(TOTAL <Country>{<MonthYear = {"$(=MonthName(Max(MonthYear)))"}>} Value)) < 3, Sum({<MonthYear = {"$(=MonthName(Max(MonthYear)))", "$(=MonthName(AddMonths(Max(MonthYear), -1)))"}>}Value))

Re: using two expressions as one expression in bar chart

Thu, 27 Oct 2016 00:04:27 GMT

yes sunny yes that is what i'm looking for the image looks good for me

can we do it in the same way for last 5 months

Re: using two expressions as one expression in bar chart

Thu, 27 Oct 2016 00:05:36 GMT

my list box is as of month here i will select values in asofmonth so will that impact the expression

Re: using two expressions as one expression in bar chart

sunny_talwar — Thu, 27 Oct 2016 00:06:44 GMT

Do you need AsOfMonth or did you just create that to make the above work? We can make this work based on selection in Month field? Is that good enough?

Re: using two expressions as one expression in bar chart

sunny_talwar — Thu, 27 Oct 2016 00:14:55 GMT

Try this expression out:

=If(Rank(Sum(TOTAL <Country>{<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month>} Value)) < 3,

Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -5), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month>}Value))

Where MonthYear is created in the script like this:

MasterCalendar:

LOAD TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: using two expressions as one expression in bar chart

Thu, 27 Oct 2016 00:40:51 GMT

Hi Sunny actually i need asof month to show some rolling sums and averages

so will this impact the set analysis you prvided

Re: using two expressions as one expression in bar chart

Thu, 27 Oct 2016 01:07:29 GMT

Hi Sunny can we convert my two expressions in the way u worte

sum({$<mydate={"=$(=max(mydate))"},IsSameMonth={'1'}>}sales)

sum({$<AsOfMonth={"$(=date(AddMonths(max(AsOfMonth),-1),'YYYY MMM'))"}sales )

Re: using two expressions as one expression in bar chart

sunny_talwar — Thu, 27 Oct 2016 01:40:33 GMT

But why do you need 2 expressions John? I have used just one expression to get the requirement, is this not the right thing you were looking for?

Re: using two expressions as one expression in bar chart

Thu, 27 Oct 2016 10:26:30 GMT

Yes sunny but its not working for me in my application

Re: using two expressions as one expression in bar chart

sunny_talwar — Thu, 27 Oct 2016 10:30:03 GMT

So unless you share you application, I am not sure what else might I be able to suggest. If you have confidentiality constraints when sharing your data, you can look here to see if this helps you:

Preparing examples for Upload - Reduction and Data Scrambling