Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anuhegde
New Contributor III

Transaformations based on Quarter

Hi all,

I have requirement in my project that if we are in the current quarter then display the sum of sales for current month in the current querter. Else display the sum of sales for the latest month in that quarter.

For example, Currently it is 2018 May and quarter 2. So, for quarter 2 the sum of sales should be displayed for only for may. Quarter 1 is alraedy over. So, for quarter 1 the sum of sales should be displayed only for for March.

In 2017, Q4, sum of sales should be displayed only for Dec and so on.

How can I do it?

1 Solution

Accepted Solutions
MVP
MVP

Re: Transaformations based on Quarter

Tested this and it worked exactly as you want

Table:

LOAD Date,

MonthName(Date) as MonthYear,

Ceil(Rand() * 100) * 10 as Sales,

If((MonthStart(QuarterEnd(Date)) = MonthStart(Date) and MonthStart(Date) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(Date), 1, 0) as Flag;

LOAD Date(MakeDate(2016, 1, 1) + IterNo() - 1) as Date

AutoGenerate 1

While MakeDate(2016, 1, 1) + IterNo() - 1 <= Today();

9 Replies
MVP
MVP

Re: Transaformations based on Quarter

May be create a flag in the script for this

If((MonthStart(QuarterEnd(DateField)) = MonthStart(DateField) and MonthStart(DateField) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(DateField), 1, 0) as Flag

and then use an expression like this

Sum({<Flag = {1}>}Sales)

duncanblaine
Contributor

Re: Transaformations based on Quarter

Hi Anusha

You could also do the followin

1. Add CalMonthStart to your Calendar object in your script. I assume you have one if you are using Quarters as a filter. eg.

         

        LOAD ....

MonthStart(TransactionDate) as CalMonthStart

Resident ..

2. Then calculate Sales as


=Sum({<CalMonthStart={'$(=Max(MonthStart(TransactionDate)))'}>}Sales)

This will always show sales for only the latest month that has recorded sales (in selected Quarter), which should be fine unless you're recording future sales.

Hope this helps.

anuhegde
New Contributor III

Re: Transaformations based on Quarter

Hi Sunny,

Thanks for the response and I tried  this.

Now I'm able to see the data for the current month in the current quarter.

But there is one problem here. For the previous quarters, it shows the data only for the last quarter in each year.

Example: I'm able to see the data only for 2017 Q4, 2016 Q4,2015 Q4 and 2016 Q4. Data for rest of the quarters in that year is not displayed.

I want the sum(Sales) for the latest month in each quarter.

Example:

2018 Q2- should display the data for May at this point and it is working.

2018 Q1- should display the data for March 2018.

2017 Q4 - Dec 2017

2017 Q3 - Sep 2017

2017Q2 - June 2017 and so on.

Could you please help me with this?

anuhegde
New Contributor III

Re: Transaformations based on Quarter

Thank you for the response. I will try

MVP
MVP

Re: Transaformations based on Quarter

Tested this and it worked exactly as you want

Table:

LOAD Date,

MonthName(Date) as MonthYear,

Ceil(Rand() * 100) * 10 as Sales,

If((MonthStart(QuarterEnd(Date)) = MonthStart(Date) and MonthStart(Date) < MonthStart(Today())) or MonthStart(Today()) = MonthStart(Date), 1, 0) as Flag;

LOAD Date(MakeDate(2016, 1, 1) + IterNo() - 1) as Date

AutoGenerate 1

While MakeDate(2016, 1, 1) + IterNo() - 1 <= Today();

anuhegde
New Contributor III

Re: Transaformations based on Quarter

Hi Sunny,

Thanks a lot. Yes it worked.

One question, I have tried in this way too and observed the same results.

if((Month(QuarterEnd([Date])) = Month([Date]) or Monthstart(Today()) = Monthstart([Date])),1,0)


Do you find any senerio where this expression would fail to give the correct results?

Once again thanks a lot for the help.

Kind regards,

Anusha.

MVP
MVP

Re: Transaformations based on Quarter

If you don't have future Months in your app, then this should work the same way. I was not sure if you have future Months or not, which is why I gave a solution which should always work.

anuhegde
New Contributor III

Re: Transaformations based on Quarter

ok. Thank you.

Also, could you please explain me what is happening in this expression?

=if(GetFieldSelections([Price Period])='Current Month','[Calendar MonthYear] = {"$(vCurrentM)"},',if(GetSelectedCount([Calendar Year]),'[Flag Yearly Data]={"Yes"},','[Flag Current Data]={"Yes"},[Flag Yearly Data]={"Yes"},'))

MVP
MVP

Re: Transaformations based on Quarter

Seems like this is creating a set analysis to be used in an expression based on different selections made in the dashboard.