Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

radupanciuc
New Contributor II

Sum based on condition with dimension

Hi guys,

I have some cost data in which I have the cost, the date the cost was posted (Posting Month) and the date the cost actually happened (Actual Month).

I would like to display in a table (Dimension is Posting Month) the % of cost that happened and is being posted in the same month (i.e. Posting Month = Actual Month).

Normally, I do this with Set Analysis but I am not sure how I can incorporate the above condition (Posting Month = Actual Month). For a single month (in this case June 2017) my formula would be:

Sum({$< [Actual Month]={'2017 06'} >} [Cost])/Sum({$< [Posting Month] = {'201706'} >} TOTAL <[BA]> [Cost])

 

Posting MonthAmount (€)Amount (%)
201701100,0000%
201702100,0000%
201703100,0000%
201704100,0000%
201705100,0000%
201706100,00046%
201707100,0000%
201708100,0000%
3 Replies
prat1507
Valued Contributor

Re: Sum based on condition with dimension

Use =if([Actual Month]=[Posting Month],Sum([Amount (€)]))/sum([Amount (€)])

Re: Sum based on condition with dimension

Hi,

I would recommend to create a flag in script to know if both the dates are in same month.

Load *,If(Monthname(PostingDate) = Monthname(ActualMonth), 1,0) as MonthFlag

From xyz;

Now in set analysis use below expression.

Sum({$<MonthFlag  = {"1"}>} [Cost])/Sum([Cost])

Regards,

Kaushik Solanki

prat1507
Valued Contributor

Re: Sum based on condition with dimension

Or

If([Actual Month]=[Posting Month],Sum( [Cost]))

/Sum(TOTAL <[BA]> [Cost])


in your case


Regards

Pratyush

Community Browser